cancel
Showing results for
Did you mean:
Frequent Visitor

## Difference Between two dates and times

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

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
-
+
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

Community Support

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
-
+
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

Announcements

#### Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

#### Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

#### Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,495)