Difference Between two dates and times

[rnunley]

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!

[v-bofeng-msft · Accepted solution]

Hi @rnunley :

Could you tell me:

• Do you want to get the number of hours between two dates (weekends)?
• Are your working hours from 8:00 AM to 5:00 PM?

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