Time between timestamps excluding certain hours

**Silaswulff** · 09-01-2011, 09:45 AM

Hi. I know this subject has been touched upon a lot in here, but i have failed to make it work for me

Basically, i have two timestamps, I want to find out how many business hours there is between them, so i would be excluding hours between 06:00 and 18:00 from timestamps such as: 08-05-2011 15:32:27 and 09-05-2011 10:10:54 not counting the 12 hours between 6PM and 6AM, but still taking into account that two timestamps can be from the same day but differet times (so just subtracting 12 hours wont work)..

Anyone can point me in the right direction ?

**arthurbr** · 09-01-2011, 10:01 AM

If first stamp in A1 and second in B1,
maybe

=IF(INT(A1)=INT(B1),B1-A1,MOD(B1-A1,1)-0,5)

**Silaswulff** · 09-01-2011, 03:21 PM

Brilliant, it worked..

Thanks a million :-)

If you have time, could you explain the formula to me ? or point me to where I can read about the functions you use ?

If you have the time..

**arthurbr** · 09-01-2011, 03:23 PM

If nobody else has done it, I'll give it a go tomorrow if you don't mind

**daddylonglegs** · 09-01-2011, 03:28 PM

Are business hours 6-18 on all days, even Saturday and Sunday? Are the timestamps always within the business hours (or could your start time be 4AM for instance....or the end time be 8PM)?

**Silaswulff** · 09-01-2011, 03:34 PM

Hmm might have been too quick there, it works with the hours but not with the days :-/

As is, It works if one day is, say, 30-08-2011 13:30 and second 31-08-2011 09:00 it counts 7:30 hours which is great, but if i change the first date to 29-08-2011 13:30 it still only counts 7:30 .. I need it to also count the full work hours of the day in between..

Any ideas ??

**Silaswulff** · 09-01-2011, 03:35 PM

@Daddylonglegs

Timestamps are always within business hours, Sat and Sun is not business hours

**daddylonglegs** · 09-01-2011, 03:47 PM

Try this version

=(NETWORKDAYS(A1,B1)-1)/2+MOD(B1,1)-MOD(A1,1)

custom format result cell as [h]:mm

note the square brackets.....

NETWORKDAYS function is part of Analysis ToolPak add-in in Excel 2003 - if formula doesn't work then activate the add-in

Tools > add-ins > tick "Analysis ToolPak" box

**Silaswulff** · 09-01-2011, 05:33 PM

The NETWORKDAYS worked like a charm, thanks :-)..

One question though:
In the odd event the business hours change, which part of the formula should i look at ?

**daddylonglegs** · 09-01-2011, 06:08 PM

Because your hours are half of the day I divided by 2 but this more generic version includes the times....

=(NETWORKDAYS(A1,B1)-1)*("18:00"-"06:00")+MOD(B1,1)-MOD(A1,1)