Need to Calculate difference between 2 dates with times

**KeithMark63** · 09-25-2012, 11:04 AM

Hi Hoping you can help out there.

I have a calculation I need to complete in Excel 2010 - I need to take a day and time difference between a start and stop stamp:

Cell - P16 22/03/2012 04:39:54
Cell - S16 23/05/2012 09:02:07

Need to get this down to number of days and hours if I use Networkdays function thatgives me days but I cant calculate time differences I could take S16-P16 format cell as [h]:mm then reference by division of 24 but there must be a simpler calculation.

Thanks in advance

**daddylonglegs** · 09-25-2012, 11:32 AM

For working days and hours you could use this formula for whole days

=NETWORKDAYS(P16,S16)-1-(MOD(P16,1)>MOD(S16,1))

and this for additional time

=MOD(S16-P16,1)

Assumes that your start and end dates will always be weekdays

If you want the days and hours in one cell try

=NETWORKDAYS(P16,S16)-1-(MOD(P16,1)>MOD(S16,1))&" days "&TEXT(S16-P16,"hh:mm")

**KeithMark63** · 09-25-2012, 12:02 PM

Thats brilliant thank you - worked really well and I can now complete a whole years of calculations in the space of minutes instead of hours!!

**KeithMark63** · 09-26-2012, 06:12 AM

OK thats worked really well and I've now just realised that I need to take htis down to working hours so the formula gives me all I need to delvier on working days and hours but I guess this is based on a 24hr day and I need to reduce to an 08:30:00 working day which is 09:00 to 17:30.

Many thanks in advance

**daddylonglegs** · 09-26-2012, 03:06 PM

OK assuming start and end times/dates will always be within the working hours this formula should give you the elapsed work hours

=(NETWORKDAYS(P16,S16)-1)*("17:30"-"9:00")+MOD(S16,1)-MOD(P16,1)

format result cell as [h]:mm

Square brackets allow the display of values > 23:59