How to adjust date/time for calculation

[mcoelho]

Hi everbody,

This is a tricky one. I want to calculate the duration of a given process. I have the date/time it starts and ends. I achieve this by subtracting one with the other. In the example below, the results are (measured in days): 0,805/0,169/7,056.

My problem is that in order to show a realistic result, the start date/time, should only count from 09:00am of the next morning. For example, on the first row below, the start should be 02-02-2010 09:00 instead of 01-02-2010 14:51.

To make things more complicated there are two exceptions to this rule: a) if the "real" start date/time is after 07:00pm then the start date should be 09:00am two days later; b) if the "real" start date/time is on a friday (any time), then the start date/time should be 09:00am of the following monday.

Is there a solution for this? I have to do these calculations on a monthly basis for around 15.000 rows. Imagine the time I would save if I could have Excel to the date adjustments automatically.

Can anybody help?

Thanks a lot.

<TABLE style="WIDTH: 284pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=379><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" span=2 width=127><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 46pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17 width=61>Reference</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 95pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=127> Start</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 95pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=127> End</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=64> Duration</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 align=right>01-02-2010 14:51</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 align=right>02-02-2010 10:10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>0,805</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 align=right>02-02-2010 10:28</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 align=right>02-02-2010 14:31</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>0,169</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 align=right>02-02-2010 15:07</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 align=right>09-02-2010 16:28</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>7,056</TD></TR></TBODY></TABLE>

 

[barry houdini]

On row 2, I shouldn't have to do anything, as the start and end date are on the same day.

so adjusting my formula, perhaps try this to get time difference where start date/time is in A2 and end date/time in B2

=C2-IF(INT(B2)=INT(C2),B2,WORKDAY(B2+"5:00",1)+"09:00")

 

[barry houdini]

....although to me the results wouldn't seem to be very logical using these rules....

In example 2 you get a time gap between start and end of 4:03 because you aren't adjusting the start time.

What if the start time was the same for that example but the end time was the next day at 10:00. Now the end time is on the next day you will adjust the start time to 09:00 on that day....so end time is later than before.....but time difference is now less - only 1:00

Also what if start time is 20:00 on Monday and end time is the next day at 13:00, what should the result be then?

 

[Fowmy]

Apologies, Fowmy, my fault, I adjusted your formula to look at B2 as the source cell but I missed one of the references.....

My only query would be whether Saturday or Sunday would be a valid adjusted start date. For example using my WORKDAY version a start time/date of Thursday at 20:00 would become a Monday start at 09:00 whereas yours would give a Saturday start at 09:00......

edit: Yes WORKDAY only adds whole days but adding 5 hours to the start time/date effectively moves the base date for WORKDAY to the next day if the time is after 19:00, otherwise it has no effect

Thanks barry,

Your WORKDAY formula does work well.

 

[mcoelho]

Thanks for all the input so far guys. But I'm confused...
Does Barry's formula work? I've tried it but I the result was:

<TABLE style="WIDTH: 340pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=452><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" span=2 width=106><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0 1pt solid; BACKGROUND-COLOR: #0070c0; WIDTH: 80pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0 1pt solid; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl65 height=18 width=106> Start</TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 80pt; BORDER-TOP: #f0f0f0 1pt solid; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl65 width=106> End</TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 41pt; BORDER-TOP: #f0f0f0 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl69 width=55>Duration</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 139pt; BORDER-TOP: #f0f0f0 1pt solid; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl67 width=185> Barry's Formula</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 80pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl66 height=18 width=106>01-02-2010 14:51</TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 80pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl66 width=106>02-02-2010 10:10</TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 41pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=55>0,805</TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 139pt; BORDER-TOP: #f0f0f0 1pt solid; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl68 width=185 align=middle>##############################</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 80pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl66 height=18 width=106>02-02-2010 10:28</TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 80pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl66 width=106>02-02-2010 14:31</TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 41pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=55>0,169</TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 139pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl68 width=185 align=middle>##############################</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 80pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl66 height=18 width=106>02-02-2010 15:07</TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 80pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 1pt solid" class=xl66 width=106>09-02-2010 16:28</TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 41pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=55>7,056

<!-- / message --></TD><TD style="BORDER-BOTTOM: #f0f0f0 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 139pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 1pt solid" id=td_post_2612897 class=xl68 width=185 align=middle>##############################</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=17 colSpan=4>C2-IF(INT(B2)=INT(C2);B2;WORKDAY(B2+"5:00";1)+"09:00")</TD></TR></TBODY></TABLE>

 

[Fowmy]

To implement barry's formula, your start dates in column B, End dates in column C and the formula should be in column D.

 

[mcoelho]

Of course Fowmy Thanks.

Barry mentioned two potential problems with his formula, which are absolutely correct, but with my data, they won't apply.

Anyway, if for some reason some of the results turn out to be negative, I will solve those cases by hand (I don't expect them to happen very often).

This was really a time-saver. I had to work this out by hand every month!!!

Thanks everybody!

 

[mcoelho]

Following-up on this, here's another challenge:

By subtracting these two cells, I'm trying to measure productivity. But people have fixed working hours (09:00 till 19:00). If I subtract say:

<TABLE style="WIDTH: 285pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=381><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" span=3 width=127><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=17 width=127 align=right>09-04-2010 17:13</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=127 align=right>10-04-2010 16:39</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=127 align=right>23,43</TD></TR></TBODY></TABLE>

The result is 23,43 hours. But to be fair, I should only count until 19:00 on the start day, and from 09:00 on the end day. The correct result would be 9,43 hours.

Can this be solved automatically?

Thanks.