Calculate number of hours/minutes in middle of time durations

[Anonymous]

Hello,

I am really hoping someone can help me on this.

I am creating an overtime form, that has start and finish times.

I need to auto calculate the number of hours from the start and finish times for the following different overtime rates.

• Overtime worked between 16:00 - 18:00 is at rate 1.0
• Overtime worked between 18:00 - 06:00 Mon - Fri is at rate 1.5
• Overtime worked on a Saturday is at rate 1.5
• Overtime worked on Sundays is 2.0

So as an example if a user inputs a start of 16:00 and a finish time of 22:00 on a weekday, it will need to populate the column's as shown below.

Hope that makes sense and of anyone can help me with the code I will very greatful.

Thanks

Dan

[Anonymous]

Hi Josh, hope you had a good weekend, and thanks once again for your help.

I did have a play over the weekend.

I wasn't sure if I could make use of variables as I'm working in a repeating galley.

But I did have some success following your examples and writing a bunch of IF statements.  All was all working great, including if it's a workday or weekend.  It all feel down when I  applied the finish time of 00:00.

I was trying to write an IF statement something like IF >18:00 and <00:00 but PowerApps was taking this as the next day, and I was getting negative results.

The solution needs to be in 15 minute increments, and I've managed to get it working up to 23:45. 

Any ideas?

Thanks

Dan

[turnerj3]

Hey Dan,

Good to hear! I would try to work on either side of midnight and subtract from 24:00 down to 18:00 and then subtract from 6:00 down to 0:00 for the next day and get the sum of the two. Something like if Finish time is 0:00, stick 24:00 in the Datediff formula instead of the finish Timestamp. I will be around my computer in a couple hours.

[Anonymous]

Oh ok, interesting. Will PowerApps recognize TimeValue 24:00?

I am in workshops all day but will give it a go tonight!

Dan

[turnerj3]

Good point, I didn't think about that. You may have to subtract from 23:59 if Finishtime =00:00. Then add 1 minute and divide by 60. 

Let me know.

Josh

[Anonymous]

OK, so been having a play and slowly getting there I think. 

So if the user selects 00:00 for the finish time it will store as 23:59.  In the formula I have added 1 min back and divide by 60.  In the example below the output should be 1 (hour).  When I add one 1 minute the output is correctly showing as 60 but when I divide by 60 it outputs 59.0166667.  

Here is my poor excuse of code I am using in the RATE 1.5 column:

//Weekday starting before 18:00 and before 23:45
If(TextInput1_2.Text = "Weekday" And

TimeValue(Finish.Text)<=TimeValue("23:45:00") And TimeValue(Start.Text)<=TimeValue("18:00:00"),
DateDiff(TimeValue("18:00:00"), TimeValue(Finish.Text),TimeUnit.Minutes)/60,

//Weekday starting after 18:00 and before 23:45
If(TextInput1_2.Text = "Weekday" And
TimeValue(Finish.Text)<=TimeValue("23:45:00") And TimeValue(Start.Text)>=TimeValue("18:00:00"),
DateDiff(TimeValue(Start.Text), TimeValue(Finish.Text),TimeUnit.Minutes)/60,


//Weekday starting before 18:00 and finish at midnight
If(TextInput1_2.Text = "Weekday" And
TimeValue(Finish.Text)=TimeValue("23:59:00") And TimeValue(Start.Text)<=TimeValue("18:00:00"),
DateDiff(TimeValue("18:00:00"), TimeValue(Finish.Text), TimeUnit.Minutes)+1/60,

//Saturday
If(Weekday(DatePicker1.SelectedDate) = 7 And TimeValue(Finish.Text)<=TimeValue("23:45:00"),
DateDiff(TimeValue(Start.Text), TimeValue(Finish.Text),TimeUnit.Minutes)/60,

//Saturday finishing at midnight
If(Weekday(DatePicker1.SelectedDate) = 7 And TimeValue(Finish.Text)=TimeValue("23:59:00"),
DateDiff(TimeValue(Start.Text), TimeValue(Finish.Text), TimeUnit.Minutes)+1/60,

//Weekday starting after 18:00 and finish at midnight
If(TextInput1_2.Text = "Weekday" And
TimeValue(Finish.Text)=TimeValue("23:59:00") And TimeValue(Start.Text)>=TimeValue("18:00:00"),
DateDiff(TimeValue(Start.Text), TimeValue(Finish.Text), TimeUnit.Minutes)+1/60))))))

Also, how can I display negative values as zero (column Rate 1.0 should be zero as start time is after 18:00)

Thanks so much!

Dan

[turnerj3]

Dan,

  I did a test yesterday and got the same 59.

To fix it close the ) after 1 then /60.

Send me the code for 1.0 that’s causing the -. 

[Anonymous]

So, I adjusted the code to close the ) after 1 then /60.  When I set the finish time to 00:00 the result is blank, it works if it is 23:45 or less.

The negative value is caused if the user inputs the wrong way round e.g. start 21:00 and finish at 17:00 - ideally it would show as zero when this happens

Here is my adjusted code:

//Weekday starting before 18:00 and before 23:45
If(TextInput1_2.Text = "Weekday" And
TimeValue(Finish.Text)<=TimeValue("23:45:00") And TimeValue(Start.Text)<=TimeValue("18:00:00"),
DateDiff(TimeValue("18:00:00"), TimeValue(Finish.Text),TimeUnit.Minutes)/60,

//Weekday starting after 18:00 and before 23:45
If(TextInput1_2.Text = "Weekday" And
TimeValue(Finish.Text)<=TimeValue("23:45:00") And TimeValue(Start.Text)>=TimeValue("18:00:00"),
DateDiff(TimeValue(Start.Text), TimeValue(Finish.Text),TimeUnit.Minutes)/60,

//Weekday starting before 18:00 and finish at midnight
If(TextInput1_2.Text = "Weekday" And
TimeValue(Finish.Text)=TimeValue("23:59:00") And TimeValue(Start.Text)<=TimeValue("18:00:00"),
DateDiff(TimeValue("18:00:00"), TimeValue(Finish.Text), TimeUnit.Minutes)+1)/60,

//Saturday
If(Weekday(DatePicker1.SelectedDate) = 7 And TimeValue(Finish.Text)<=TimeValue("23:45:00"),
DateDiff(TimeValue(Start.Text), TimeValue(Finish.Text),TimeUnit.Minutes)/60,

//Saturday finishing at midnight
If(Weekday(DatePicker1.SelectedDate) = 7 And TimeValue(Finish.Text)=TimeValue("23:59:00"),
DateDiff(TimeValue(Start.Text), TimeValue(Finish.Text), TimeUnit.Minutes)+1)/60,

//Weekday starting after 18:00 and finish at midnight
If(TextInput1_2.Text = "Weekday" And
TimeValue(Finish.Text)=TimeValue("23:59:00") And TimeValue(Start.Text)>=TimeValue("18:00:00"),
DateDiff(TimeValue(Start.Text), TimeValue(Finish.Text), TimeUnit.Minutes)+1)/60)))

Cheers 

Dan

[turnerj3]

Try to add some additional if logic. Something like

If(txtbox1.0 <0, txtbox1.0=0).

Not sure exactly, I can try it soon.

[Anonymous]

Thanks! I'm struggling to write this logic with all the IF statements going on! Dan

[turnerj3]

I was afraid it was going to get pretty complicated.

I did a test though. You cannot change the value of the calculation inside the if statement it gives an error.

The way to make it work would be to leave the if statements that you have working with the negative number, but turn the label or textbox visible to false.

Then put a new label or textbox on top of it and put this logic that looks at the invisible one.

If(Value(Label13_2.Text)<0,0,Label13_2.Text)

Change Label13_2 to whatever your invisible textbox is named.