DATE/TIME Formula

[kparadise]

Hello,

I have a column which contains DATE/TIME (3/14/12 1:30 PM) in excel. This column is called "Arrival Time". What I am trying to do is to look at this column and determine what the "Processing Date" should be.

Here is the caveat. Files arrived from 8am - 7:59am the next day are processed that next day. (i.e. any file arrived on 1/1/2018 from 8am through 1/2/2018 7:59am will be processed on 1/2/2018). Another example - any file which has an "Arrival Time" on or after 7/4/2018 8:00am - 7/5/2018 7:59am will have a "Processing Date" of 7/5/2018.

 

[steve the fish]

Try this:

=IF(MOD(A1,1)<8/24,INT(A1),INT(A1)+1)

 

[steve the fish]

Heres same but a bit shorter:

=INT(A1)+(MOD(A1,1)>=8/24)

 

[kparadise]

How would this formula change if I had to include WORKDAY FUNCTION?

So if the "Arrival Time" fell on a weekend or holiday, the Processing Date would have to be the next weekday, or the day following the holiday?

 

[steve the fish]

Try this:

=WORKDAY(INT(A1)+(MOD(A1,1)>=8/24)-1,1)

 

[kparadise]

This seems to be working great; can you explain the formula.

Also, if I wanted to change the formula to do 6:00am - 5:59am.....do I just change that formula to 6/24?

 

[steve the fish]

Within the workday function we have our start date as the date we originally calculated but take a day from it. The minus 1 you see in the formula. We then add a day in the workday function which will then produce exactly the same day as original (-1+1 is 0) if that is a workday. If it isnt it will produce the next workday. In answer to your 2nd question yes 6/24 would work for 6am.