Date/Time Function Help

[smonczka]

I am working with date/time functions trying to figure out how many
hours were spent doing a job. I have a start time and a finish time.
But I need to subtract out any time between five pm and six am and
weekends from the times. These would be times that no one was actually
working on the job. For example I have

4/5/2005 18:23 4/6/2005 17:04

Could someone give me a clue as to how this might be done?

Thanks,
Steve Monczka
(e-mail address removed)

 

[Gnasher]

Reply by email. Test out this spreadsheet. HTH JY

 

[Jerry W. Lewis]

Assuming that the start and end times are in A1:B1, then
=B1-A1
gives the elapsed time when formatted with a custom "[h]:mm" format.

Assuming that neither start nor end times are outside working hours,
then you would subtract off
(INT(B1)-INT(A1))*(13/24)
Excel times are stored as fractions of days, hence the time between 5pm
and 6am is 13/24. Curiously, both your example start and end times are
outside of working hours, so you may need adjustments on the starting
and ending days.

NETWORKDAYS() can be used to identify and remove weekends days.

Jerry