cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sababneh
Level 8

Adding working days and hours to a date

Hey all,

 

Can you please give me an advice on how to do it?

 

First question:


I have a date for example:

14.3.2018 + 3 working days = 19.3.2018

Because here the weekends are Friday & Saturday

 

How can I make this possible in PowerApps?

 

Second question:

 

I have a date and time for example:

14.3.2018 2:00 PM + 3 working hours = 15.3.2018 9:30 AM

Because here we work from 8:00 AM till 3:30 PM

 

How can I make this possible in PowerApps?

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Sienna
Level 10

Re: Adding working days and hours to a date

Hi @Sababneh

This is actually really tricky task. But I like challenges 🙂 I have just quickly made up something just for dates. I will look at it later to make the code little lighter. I'm sure we can do better then this. You will have to disable selecting Fridays and Saturdays in your app though!

DateAdd(DatePicker1.SelectedDate,
Value(TextInput1.Text) +
RoundDown(Value(TextInput1.Text) / 5, 0)*2+
Switch(Weekday(DatePicker1.SelectedDate),5,If(Mod( Value(TextInput1.Text) , 5)>0,2,0),
4,If(Mod( Value(TextInput1.Text) , 5)>1,2,0),
3,If(Mod( Value(TextInput1.Text) , 5)>2,2,0),
2,If(Mod( Value(TextInput1.Text) , 5)>3,2,0),
1,If(Mod( Value(TextInput1.Text) , 5)>4,2,0))
)


Second question

Yes you can do that too but that is another challenge so be patient 🙂

View solution in original post

Sienna
Level 10

Re: Adding working days and hours to a date

Ok so I think I'm done. Please test it and see if it calculate correctly. Basically it is the same principle as adding days but little more tricky. I have separated the code into a variable so it is easier to read. Insert same items like on the picture

HoursAdd.png

In the button "calculate" insert in OnSelect property

UpdateContext({diference: 60*Value(StartHour.Text)+Value(StartMinutes.Text)-480,
duration:60*Value(DurHours.Text)+Value(DurMinutes.Text)}); UpdateContext({calcDays:RoundDown((diference+duration)/450,0),
calcMinutes: Mod((diference+duration),450)})

and in your result label change Text property to

Text(
DateAdd(
DateAdd(DatePicker1_1.SelectedDate,
Value(calcDays) + 
RoundDown(calcDays / 5, 0)*2+
Switch(Weekday(DatePicker1_1.SelectedDate),5,If(Mod( calcDays , 5)>0,2,0),
                                           4,If(Mod( calcDays , 5)>1,2,0),
                                           3,If(Mod( calcDays , 5)>2,2,0),
                                           2,If(Mod( calcDays , 5)>3,2,0),
                                           1,If(Mod( calcDays , 5)>4,2,0))
),480+calcMinutes,Minutes),
"[$-en-GB]dd/mm/yyy hh:mm")

Obviously you will have to disable the "calculate" button if the start times exceeds working hours and datepicker when friday or saturday is selected. I can't help you with flows but I assume you can trigger the flow when calcDays>0

I think it would be nice to have a function for this also with excluding certain days with DateDiff which I think is very useful for business use. I have already posted the idea for excluding certain days with DateDiff here

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Exclude-weekends-from-DateDiff/idi-p/86272

so you can post the idea to add a function for this scenario

 

View solution in original post

13 REPLIES 13
Highlighted
Sienna
Level 10

Re: Adding working days and hours to a date

Hi @Sababneh

This is actually really tricky task. But I like challenges 🙂 I have just quickly made up something just for dates. I will look at it later to make the code little lighter. I'm sure we can do better then this. You will have to disable selecting Fridays and Saturdays in your app though!

DateAdd(DatePicker1.SelectedDate,
Value(TextInput1.Text) +
RoundDown(Value(TextInput1.Text) / 5, 0)*2+
Switch(Weekday(DatePicker1.SelectedDate),5,If(Mod( Value(TextInput1.Text) , 5)>0,2,0),
4,If(Mod( Value(TextInput1.Text) , 5)>1,2,0),
3,If(Mod( Value(TextInput1.Text) , 5)>2,2,0),
2,If(Mod( Value(TextInput1.Text) , 5)>3,2,0),
1,If(Mod( Value(TextInput1.Text) , 5)>4,2,0))
)


Second question

Yes you can do that too but that is another challenge so be patient 🙂

View solution in original post

Sababneh
Level 8

Re: Adding working days and hours to a date

Hi @Sienna

 

Thats pretty amazing code actually!

 

Its been an hour trying to understand what you've did there, its art Smiley Happy

And it worked perfectly.

 

Now I'm trying to make it happen for working hours, and it would be really appreciated if you could do that too Smiley Happy

 

And if possible, how can I make Fridays and Saturdays greyed out in DatePicker1


Thanks!

Sienna
Level 10

Re: Adding working days and hours to a date

@Sababneh

You can't grey out days in native date picker. You have 2 options you can do

 

1/ Build your own datepicker by modifying the one I already posted here

https://powerusers.microsoft.com/t5/Creating-Apps/Custom-Date-Picker/m-p/89412#M1183

 

2/ Grey out submit button or give a message to user by changing DisplayMode property to

If(Weekday(DatePicker1.SelectedDate)>5,DisplayMode.Disabled, DisplayMode.Edit)

About the last point. How many hours will you be usually adding into it? Is it gonna exceed 1 working day?

Sababneh
Level 8

Re: Adding working days and hours to a date

@Sienna

 

What an amazing person you are Smiley LOL!

 

I like how you treated the Friday and Saturday issue!

And about the working hours, yeah minimum is 4 working hours, and maximum is 54 working hours.

 

Thank you Smiley Happy

Appreciated.

Sienna
Level 10

Re: Adding working days and hours to a date

@Sababneh

 

Thanks 🙂

 

I still need more information. Do you want to include hours adding to days adding or the hours adding is separated issue and you want to add only hours based on what you said? How do you want to specify the start time? is it already specified or you want to specified it too?

Sababneh
Level 8

Re: Adding working days and hours to a date

@Sienna

 

What I'm trying to acheive is.

 

If there is a task in our company that is at:

 

15.3.2018 02:00 PM

 

And the task needs for example 4 hours to be accomplished, to give an expected time of completion:

 

18:3:2018 09:30 AM

 

Because the working hours are from 08:00 AM till 03:30 PM, and Friday and Saturday is weekend.

 

I'm only working with hours. The days adding was for another app that you thanfuly solved Smiley Happy

 

 

Sienna
Level 10

Re: Adding working days and hours to a date

So basically you will specify in your app when the work will start and how many hours it will take. After that you want the result what date and time it will finish. Right? I think I got it so I will take a look at it later today... so be patient 🙂

Sababneh
Level 8

Re: Adding working days and hours to a date

@Sienna

 

Yes exactly.

 

And if they exceed that day. Mircrosoft Flows will send escalation emails to the manager Smiley Tongue

Sienna
Level 10

Re: Adding working days and hours to a date

Ok so I think I'm done. Please test it and see if it calculate correctly. Basically it is the same principle as adding days but little more tricky. I have separated the code into a variable so it is easier to read. Insert same items like on the picture

HoursAdd.png

In the button "calculate" insert in OnSelect property

UpdateContext({diference: 60*Value(StartHour.Text)+Value(StartMinutes.Text)-480,
duration:60*Value(DurHours.Text)+Value(DurMinutes.Text)}); UpdateContext({calcDays:RoundDown((diference+duration)/450,0),
calcMinutes: Mod((diference+duration),450)})

and in your result label change Text property to

Text(
DateAdd(
DateAdd(DatePicker1_1.SelectedDate,
Value(calcDays) + 
RoundDown(calcDays / 5, 0)*2+
Switch(Weekday(DatePicker1_1.SelectedDate),5,If(Mod( calcDays , 5)>0,2,0),
                                           4,If(Mod( calcDays , 5)>1,2,0),
                                           3,If(Mod( calcDays , 5)>2,2,0),
                                           2,If(Mod( calcDays , 5)>3,2,0),
                                           1,If(Mod( calcDays , 5)>4,2,0))
),480+calcMinutes,Minutes),
"[$-en-GB]dd/mm/yyy hh:mm")

Obviously you will have to disable the "calculate" button if the start times exceeds working hours and datepicker when friday or saturday is selected. I can't help you with flows but I assume you can trigger the flow when calcDays>0

I think it would be nice to have a function for this also with excluding certain days with DateDiff which I think is very useful for business use. I have already posted the idea for excluding certain days with DateDiff here

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Exclude-weekends-from-DateDiff/idi-p/86272

so you can post the idea to add a function for this scenario

 

View solution in original post

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,506)