DATE/TIME Formula

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
176
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.
 

Some videos you may like

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
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Try this:

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Heres same but a bit shorter:

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

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
176
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
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this:

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

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
176
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
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,313
Messages
5,527,952
Members
409,794
Latest member
ajithppajith

This Week's Hot Topics

Top