Reference Time in If Statements

[#1]

Hi,

I am trying to find out the number of days it takes to ship orders - but any order placed after 4 pm cannot be shipped the same day and therefore should not have to count the actual CompletedDate as a day.

In essence, if an order that is placed at noon, and ships the same day should have 0 days to ship, but an order placed that same day at 8 pm, then ships the following day should also show 0 days.

I am trying to use an If Statement, so that if the order is placed past 4 pm, then it takes one day off of the shipping days.

Example:

CompletedTime	CompletedDate	ShipDate
8:00:00 AM	1/1/2015	1/1/2015
5:00:00 PM	1/1/2015	1/2/2015
12:00:00 PM	1/1/2015	1/5/2015

The first two entries should both have 0 days to ship - while the last one should have 5.

My problem is referencing time in my If Statement - it doesn't want to recognize the various formats I am trying to use.

IF(CompletedTime>='4:00:00 PM',(Date(ShipDate)-Date(CompletedDate) -1), Date(ShipDate)-Date(CompletedDate))

It keeps telling me all my orders are after 4 pm, when they aren't. (I tested this by doing an If statement like above, only where the then condition just told me "After 4" instead of doing the equation.)

[stevedark · Accepted solution]

Hi Andrea,

You need to keep in mind that with a data, if it's stored correctly, that a day is actually a value of 1, and a time is a decimal portion of that, e.g. midday is .5.

Given this, you can work out the time to compare to with a sum:

if(CompletedTime >= (1/24*16),

This assumes that the CompletedTime field only has the time in, and not date time.  If it is date time the code becomes:

if((CompletedTime - DayStart(CompletedTime)) >= (1/24*16),

Here you are effectively removing all the days from the time value (i.e. up to midnight at the start of the day) leaving only the decimal portion.

I hope that all makes sense?

Steve

[#3]

Hi, that worked! And thank you for adding the part with a date and timestamp - since I did have a date in there as well.

[stevedark]

That was just a lucky guess!

Glad it's working now.

[MarcoWedel]

Hi,

I was just about to post a (wrong) solution suggesting that you might be comparing strings rather than numbers due to a non-matching TimeFormat variable, when I found your thread already answered correctly.

Just some thoughts to supplement the solution:

CompletedTime - DayStart(CompletedTime)

might as well be calculated as

Frac(CompletedTime)

which extracts the (fractional) time part from a timestamp value.

• 
  

Date(ShipDate)-Date(CompletedDate)

should be the same as

ShipDate-CompletedDate

since the formatting Date()-function does not change the numerical value of a date/timestamp,

hope this helps

regards

Marco