# DATE/TIME Formula

##### Board Regular
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.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### steve the fish

##### Well-known Member
Try this:

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

#### steve the fish

##### Well-known Member
Heres same but a bit shorter:

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

##### Board Regular
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

##### Well-known Member

Try this:

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

##### Board Regular
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?

Last edited:

#### steve the fish

##### Well-known Member
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.

Replies
7
Views
91
Replies
1
Views
46
Replies
1
Views
48
Replies
12
Views
112
Replies
0
Views
51