Date Time

[anirdesh]

If Reported Date is a workday (8/5/2016) and occurred before 6 AM (between 12:00:01 AM – 5:59:59 AM), then the New Date = 8/5/2016 @ 6 AM
If Reported Date is a workday (8/5/2016) and occurred between 6 AM – 6 PM (11:00:00 AM), then the New Date = 8/5/2016 @ 11 AM
If Reported Date is a workday (Friday 8/5/2016) and occurred after 6 PM (10:00:00 PM), then the New Date = 8/8/2016 @ 6 AM
If Reported Date is a workday (Thursday 8/4/2016) and occurred after 6 PM (10:00:00 PM), then the New Date = 8/5/2016 @ 6 AM …Next business day @ 6 AM
Or
If Reported Date is a weekend (Saturday 8/6/2016) and occurred anytime, then the New Date = 8/8/2016 @ 6 AM ……Next business day @ 6 AM


How can I get this resolved?

[Sinon05]

Assuming your date is in column A and your time in column B try:

=IF(WEEKDAY(A2,2)>5,TEXT(WORKDAY(A2,1),"dd/mm/yyyy"),TEXT(IF(HOUR(B2)>18,WORKDAY(A2,1),A2),"dd/mm/yyyy"))&IF(AND(HOUR(B2)>=6,HOUR(B2)<=18)," @ "&IF(HOUR(B2)>12,HOUR(B2)-12,HOUR(B2))&" PM"," @ 6 AM")

There's 2 parts to the formula, one that looks at the date to see if it's a weekday and also looks at whether it's after 6 PM. - IF(WEEKDAY(A2,2)>5,TEXT(WORKDAY(A2,1),"dd/mm/yyyy"),TEXT(IF(HOUR(B2)>18,WORKDAY(A2,1),A2),"dd/mm/yyyy"))
The second looks at times to return either @6 AM or the actual hour between 6 AM and 6 PM. - IF(AND(HOUR(B2)>=6,HOUR(B2)<=18)," @ "&IF(HOUR(B2)>12,HOUR(B2)-12,HOUR(B2))&" PM"," @ 6 AM")

if you have both date and time in only 1 cell, you can get the time in a separate cell with

=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))

If you're working with the American date format, change WEEKDAY(A2,2) to WEEKDAY(A2,1) and change "dd/mm/yyyy" (there are 2 instances in the formula) to "mm/dd/yyyy" or whatever format you want. There might be other tweaks to get this in american format but I only work with the European format and can't be bothered to change it.

[dflak]

Assuming that the date-time is in Column A, here's the formula:
=WORKDAY(INT(A2),IF(OR(WEEKDAY(INT(A2),2)>5,+ISNUMBER(MATCH(INT(A2),Holidays,0)),(A2-INT(A2))>18/24),1,0),Holidays)+(IF((IF(OR(WEEKDAY(INT(A2),2)>5,+ISNUMBER(MATCH(INT(A2),Holidays,0)),D2>18/24),1,0))=1,6/24,IF((A2-INT(A2))<6/24,6/24,IF((A2-INT(A2))>18/24,6/24,(A2-INT(A2))))))

Now if you want to know how this formula came about. See the attached workbook that has the helper cells that put it together.

First of all you need an understanding on how Excel does date-times. A date is simply a whole number of days since Jan 1, 1900. The time of day is a fraction of the day, so 12 PM (noon) is 0.5 and 6 AM is 0.25 and 6 PM is 0.75. For the purposes of this exercise, I expressed these values as 6/24 and 18/24.

The DOW columns are gratuitous: they are not used in the calculations but the help to verify the results.

Column A contains the date-time to be evaluated.

The date time is separated to a Date in column C =INT(A2) (The whole number part)

And a time in column D =A2-INT(A2) (The fraction part)

The column called weekend has the formula =IF(OR(WEEKDAY(C2,2)>5,+ISNUMBER(MATCH(C2,Holidays,0)),D2>18/24),1,0).

The title is a bit misleading, but here is the logic. There are three things that can “bump” a time into the next day:
- The date time falls on a weekend
- The date time is on a holiday
- The date time is after 6 PM

This formula assigns a 1 if any of these conditions are met and a zero otherwise.

New Time has the formula =IF(E2=1,6/24,IF(D2<6/24,6/24,IF(D2>18/24,6/24,D2)))

Which says, if you got bumped to the next day, use 6 AM. Otherwise if before 6 AM use 6 AM or if greater than 6 PM use 6 AM (the next day – this is redundant given that we already took care of this case), or otherwise (later than 6 AM, earlier than 6 PM) just use the time.

New Date Time is =WORKDAY(C2,E2,Holidays)+F2
In other words, if E2 is 0, it’s the same day, if E2 is 1 then it’s the next work day. The holidays are a static named range (called Holidays) defined on Sheet2.

Take this simple formula and substitute back the parts such as C2 = INT(A2) so the formula now becomes: =WORKDAY(INT(A2),E2,Holidays)+F2. Keep substituting formulas until the formula is defined only in terms of column A. That gives you the hairy formula at the beginning of this post.

Personally, I like to stick with the helper columns since they are easier to read and debug.