Working out Networkdays in hours

[kylerisi]

Hi Team,

I currently have a formula which works out the number of hours passed between a particular date and Now, i really need this to be worked out in business hours much like =networkdays, can anyone please provide me with some help on how to achieve this.

below is the formula i am currently using

=if(O6="","",(if($Q6="C","",(((NOW()-O6)*24)))))

currently my formula checks 06 for "C" if true it stays blank, if False, it then works out how many hours have passed between a cell in another sheet and the current time.

ideally if i could work out the number of ours between 9-6 monday -friday, that would be super amazing.

I have browsed other posts on this forum, but I am confused on how to embed the NOW() part into my formula

any help would be greatly appreciated.

Best Regards

Kyle

 

[gaz_chops]

Why not use networkdays * number of business hours per day.

 

[kylerisi]

I started to think along these lines, i tried replacing NOW with NEWTWORKDAYS, but I received a N/A error, I just don't know how to add this into my formula, if you could help i would appreciate it.

 

[gaz_chops]

Try

=if(or(O6="",$Q6="C"),"",NETWORKDAYS(O6,NOW())*7.5)

Change the 7.5 to your daily working hours.

 

[kylerisi]

This unfortunately does not work. The time in O6 is 30/10/2015 14:57, the formula counts this as 1 full day regardless of the time - i.e 7.5 hours. It should be 3 hours and 3 minutes. since we stop business at 6pm

 

[gaz_chops]

Then I would deduct 2 days off the network days part, then add the calculated difference for the other 2 days, e.g. normal finish time minus 14:57 & now() - normal start time.

 

[kylerisi]

Thanks Gaz_Chops.

Unfortunately I don't know how to construct this into my formula.

Best Regards

Kyle

 

[gaz_chops]

Something like

=if(or(O6="",$Q6="C"),"",((NETWORKDAYS(O6,NOW())-2)/24*7.5)+(MOD(NOW(),1)-"09:00")+("17:00"-MOD(O6,1)))

This will calculate the work days between the 2 dates, minus 2, then add the hours between 30/10/2015 14:57 & "17:00" and add the hours between "09:00" & now()

Change the "09:00" & "17:00" to be your start/finish times.

Format the cells as [h]:mm

 

[kylerisi]

Hi Gaz_Chop,

This didn't work, it returns a value of around 19:00 hours. could the fact that I'm using google sheets be an issue here? I have not had any problems with using excel formulas on the Sheets platform before.

Regards

 

[gaz_chops]

I've never used google sheets, so not sure if that would be an issue!

Did you copy & paste my formula, if not post the formula you entered.