Calculate exact amount of hours between two timestamps in excel excluding the weekends

1
votes

I'm trying to calculate the exact amount of hours between two timestamps in excel excluding the weekends. The timestamps are in the following format:

"yyyy-mm-dd hh:mm:ss"

I have tried using following formula, which seems to work fine, except for when my end date is in the weekend or on Mondays. In those cases it gives me a negative value even though end date is after start date.

=NETWORKDAYS([@start_date]];[@end_date])-1-MOD([@Start_date];1)+MOD([@end_date];1)

Example:

Start date ;    end date ;      Time difference

2017-09-30 06:47:00 ;   2017-09-30 07:19:16 ;   -0,977592593

2017-08-13 19:30:00 ;   2017-08-14 04:37:46 ;   -0,619606481
2
excelexcel-formula
in your first case, they are the same date. the result of just the networkdays() portion results in 0. you then -1 to it which is going to give you a negative number. Do not use -1 – Forward Ed
I'm confused as to why this is an issue. Presumably you are making this formula to track something related to a work schedule for people who work during the week (hence why you want to exclude weekends from formula), but if your people only ever work during the week, you shouldn't have to worry about inputs to your formula that are on weekends. – ImaginaryHuman072889
Hi, This is to track a KPI. The clock starts ticking on the start timestamp and stops during the end timestamp. All hours in the weekend do not count towards the KPI target. The difference between start timestamp and end timestamp should be below 48H. But I'd like to have the exact number to calculate more details on the performance. My current formula shows me the amount of days between start and end timestamp. So for example 0,5 = 12 hours. By removing the "-1" I will add 24 hours to everything. Which gives me incorrect values. The formula works fine during weekdays but not in the weekend... – bvanb
Is there another better formula I should be using? – bvanb
The networkdays formula disregards weekend days. This causes an issue when my start or end date is during the weekend... I need a formula that starts the clock on Monday morning when the start date begins during the weekend or stops on Friday evening when the end date is during the weekend. If that makes sense... – bvanb

2 Answers

3
votes

The suggestion from ian0411 is along the right lines but that assumes daily hours 8:30 to 17:30. I think you want to count all weekday hours in which case you can simplify with this version

=NETWORKDAYS([@start_date];[@end_date])+NETWORKDAYS([@end_date];[@end_date])*(MOD([@end_date];1)-1)-NETWORKDAYS([@start_date];[@start_date])*MOD([@start_date];1)

1
votes

Found here and this is the formula to try:

=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30")