I am creating a time off request app. Part of the app is to calculate the number of work hours missed. I have two date/time pickers and would like to calculate the number of hours between the dates/times, taking into consideration an 8 hour workday and excluding weekends. For example, the start date and time is 5/7/2021 at 2:00PM and the end date/time is 5/10/2021 at 5:00PM. The total work hours missed would be 3hrs on 5/7 and 8hrs on 5/10. 5/8 and 5/9 is a weekend so these days would not be factored into the work hours missed formula. Thank you for any direction you can give me. Thanks!
Solved! Go to Solution.
Hi @rnunley :
Could you tell me:
If so please try this solution:
1\I assume the start date time and the end date time are:
Set(StartDateTime,Date(2021,5,7)+Time(14,0,0));
Set(EndDateTime,Date(2021,5,10)+Time(17,0,0))
2\Then you could use this formula to get the hours between two dates
If(DateDiff(StartDateTime,EndDateTime,Days)>=1,
(DateDiff(StartDateTime,EndDateTime,Days)-1)*8
-
Sum(ForAll(Sequence(DateDiff(StartDateTime,EndDateTime,Days)-1,1),If(Weekday(DateAdd(StartDateTime,Value,Days))=1 || Weekday(DateAdd(StartDateTime,Value,Days))=7,1,0)),Value)*8
+
If(Weekday(StartDateTime)=1 || Weekday(StartDateTime)=7 ,0,If(17-Text(StartDateTime,"[$-en]HH")>=8,8,(17-Text(StartDateTime,"[$-en]HH"))))
+
If(Weekday(EndDateTime)=1 || Weekday(EndDateTime)=7 ,0,If(Text(EndDateTime,"[$-en]HH")-8>=8,8,(Text(StartDateTime,"[$-en]HH"))-8)),
If(Weekday(StartDateTime)=1 || Weekday(StartDateTime)=7 ,0,DateDiff(StartDateTime,EndDateTime,Hours)))
The key is to calculate the start and end dates separately, because the start and end dates are not a complete 24 hours
Best Regards,
Bof
Hi @rnunley :
Could you tell me:
If so please try this solution:
1\I assume the start date time and the end date time are:
Set(StartDateTime,Date(2021,5,7)+Time(14,0,0));
Set(EndDateTime,Date(2021,5,10)+Time(17,0,0))
2\Then you could use this formula to get the hours between two dates
If(DateDiff(StartDateTime,EndDateTime,Days)>=1,
(DateDiff(StartDateTime,EndDateTime,Days)-1)*8
-
Sum(ForAll(Sequence(DateDiff(StartDateTime,EndDateTime,Days)-1,1),If(Weekday(DateAdd(StartDateTime,Value,Days))=1 || Weekday(DateAdd(StartDateTime,Value,Days))=7,1,0)),Value)*8
+
If(Weekday(StartDateTime)=1 || Weekday(StartDateTime)=7 ,0,If(17-Text(StartDateTime,"[$-en]HH")>=8,8,(17-Text(StartDateTime,"[$-en]HH"))))
+
If(Weekday(EndDateTime)=1 || Weekday(EndDateTime)=7 ,0,If(Text(EndDateTime,"[$-en]HH")-8>=8,8,(Text(StartDateTime,"[$-en]HH"))-8)),
If(Weekday(StartDateTime)=1 || Weekday(StartDateTime)=7 ,0,DateDiff(StartDateTime,EndDateTime,Hours)))
The key is to calculate the start and end dates separately, because the start and end dates are not a complete 24 hours
Best Regards,
Bof
User | Count |
---|---|
253 | |
109 | |
92 | |
48 | |
37 |