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]

So what should the result be for row 2 of your example? If you change the start to 09:00 next day then the start time is later than the end time - would you expect zero as the result in that case (or even a negative number)?

 

[dvananthan]

hi

like barry pointed out there seems to be an anamoly in the way you calculate the start time

But in any case if you want the start time to adjusted you may try this formula in a column 'as a realistic start date" and do calculations based on this

=INT(IF(WEEKDAY(b2)=6,b2+2,b2))+1+9/24

regards

ananth

P.S. i would like comments and improvements on this

 

[Fowmy]

Another possibility:

=INT(C3)+TIME(9,,)+1+IF(WEEKDAY(C3)=6,2,IF(MOD(C3,1)>TIME(19,,),1,0))

 

[barry houdini]

Hello ananth, I don't think you are taking this part into account

....a) if the "real" start date/time is after 07:00pm then the start date should be 09:00am two days later

Fowmy, when I tried yours it made a Friday into a Tuesday.....

Can you have weekend starts, what rules do they follow? Depending on your answer you might be able to use WORKDAY function, i.e.

=WORKDAY(B2+"5:00",1)+"09:00"

 

[dvananthan]

hi barry

very true!! and sorry i totally missed it. now that you have highlited it. is it possible to modify my formula? can you do it just to get myself trained that is all.

regards

ananth

 

[Fowmy]

Hi barry,

I tested the start date as 5/2/2010 then the result was 8/2/2010 9:00 am. that was was a Monday. Could you give me the date with Friday that resulted in a Tuesday output?

 

[Fowmy]

Hi barry,

I tested the start date as 5/2/2010 then the result was 8/2/2010 9:00 am. that was was a Monday. Could you give me the date with Friday that resulted in a Tuesday output?

It was a good point you made "Can you have weekend starts, what rules do they follow?"

Also, I would like to know the "5:00" in your suggested workday function, I taught the workday will add the whole days .

=WORKDAY(B2+"5:00",1)+"09:00"

 

[mcoelho]

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

 

[barry houdini]

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