How to adjust date/time for calculation

mcoelho

New Member
Joined
Jun 7, 2009
Messages
24
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Oct 25, 2010
Messages
205
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

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

ananth

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

Fowmy

Well-known Member
Joined
Jul 10, 2008
Messages
506
Another possibility:

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Oct 25, 2010
Messages
205
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

Well-known Member
Joined
Jul 10, 2008
Messages
506
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

Well-known Member
Joined
Jul 10, 2008
Messages
506
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

New Member
Joined
Jun 7, 2009
Messages
24
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Apologies, Fowmy, my fault, I adjusted your formula to look at B2 as the source cell but I missed one of the references.....:oops:

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
 
Last edited:

Forum statistics

Threads
1,081,563
Messages
5,359,618
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top