Date formula that rounds to next day

[BMil8]

Hello. I've been asked to create an analysis that calculates the number of days since an order was placed. However, if that order was placed after 2:00pm it should be considered as if it came in the following day. For example, the below to orders came in within a few hours of each other. My current formula for days open would group them both in the "3 day" bucket. However, since that second order came in after 2:00pm (or 14:00:00), I want it to fall into a "2 day" bucket. what's the best approach for this?

Order Date	Current Date	Exact Days Open	Days Open
2022-05-20 12:31:16	2022-05-24 11:19:31	3 days 22 hrs 48 mins 15 secs	3
2022-05-20 14:41:12	2022-05-24 11:19:31	3 days 20 hrs 38 mins 19 secs	3

 

[Gerald Higgins]

How are you calculating "Days Open" at the moment ?

This would give you a result of 3 for the first entry, and 2 for the second.
Assumes Order Dates in col A, Current Date in col B -

=INT(B1)-INT(A1)-1-(MOD(A1,1)>(14/24))

 

[BMil8]

How are you calculating "Days Open" at the moment ?

This would give you a result of 3 for the first entry, and 2 for the second.
Assumes Order Dates in col A, Current Date in col B -

=INT(B1)-INT(A1)-1-(MOD(A1,1)>(14/24))

It was just a simple subtraction formula rounded down. Your formula seems to work. What is it doing?

 

[Gerald Higgins]

Your date and time information consists of numbers, for example 20/05/2022 14:41 is held by Excel as the number 44701.611944... where 44701 gives the date, and 0.611944... gives the time.

The INT function converts this value to 44701, and so cuts off the numbers after the decimal point - so giving us the date on its own, and ignoring the time element.

So we then subtract the two dates from each other, and add 1 because your results indicate that's what you want to do.

We then need to work out if the time on the Order was after 14:00 or not.
So the MOD function does something similar here to the INT function, but instead strips off the date information to leave us with just the time.
It then says, is this value greater than 14/24 (24 hours in the day), and if it is, that gives us a TRUE value, which is treated as a 1 value, which is then deducted from the earlier result.

Try using Excel's formula evaluation tool on a couple of different input values, to understand better how this works.

 

[BMil8]

How are you calculating "Days Open" at the moment ?

This would give you a result of 3 for the first entry, and 2 for the second.
Assumes Order Dates in col A, Current Date in col B -

=INT(B1)-INT(A1)-1-(MOD(A1,1)>(14/24))

I think the only extra thing I had to do was account for anything if the result was less than 24 hours. This returns a result of -1 days. So I just added an IF statement in there. Sound right?

 

[Gerald Higgins]

I think the only extra thing I had to do was account for anything if the result was less than 24 hours. This returns a result of -1 days. So I just added an IF statement in there. Sound right?

Can you give us an example of that please ?

 

[BMil8]

Can you give us an example of that please ?

ORDERDATE	Current Date	Exact Days Open	Days Open
2022-05-23 17:31:13	2022-05-24 11:19:31	0 days 17 hrs 48 mins 18 secs	-1

Then if I change the formula to =IF(INT(B1)-INT(A1)-1-(MOD(A1,1)>(14/24))=-1,0,INT(B1)-INT(A1)-1-(MOD(A1,1)>(14/24))), it shows me 0 days.

 

[Gerald Higgins]

Hi - if that gives you the results you want, then fine, go with that, although it looks a bit clunky.
You might be able to simplify it a bit.
But it looks like the rule set that you are trying to apply here is perhaps a little more complicated than it first appears, so I wouldn't like to be certain at this stage.