Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.)
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
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
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.
That was just a lucky guess!
Glad it's working now.
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