Adding working days and hours to a date

[Anonymous]

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?

[Sienna · Accepted solution]

Hi @Anonymous

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 🙂

[Anonymous]

Hi @Sienna

Thats pretty amazing code actually!

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

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

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

Thanks!

[Sienna]

@Anonymous

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?

[Anonymous]

@Sienna

What an amazing person you are !

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 

Appreciated.

[Sienna]

@Anonymous

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?

[Anonymous]

@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 

[Sienna]

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 🙂

[Anonymous]

@Sienna

Yes exactly.

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

[Sienna · Accepted solution]

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

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