Adding 10 minutes to current time - issue at midnight

Hi all,

In one of the spreadsheets there is a need to have a 'action at' field.

This is the next five minute time slot between 5 and 10 minutes in the future (eg 10.51 -> 11.00 , 1.56 ->2.05 etc)

this is fine using this formula

Please Login or Register  to view this content.

'DATE_TO_USE' is the day we should use (changes after 23.50)

Please Login or Register  to view this content.

these combined work fine for every period from 00.00 to 23.54.59 but in the next five minute period point to 00.05 on the current day (as opposed to day + 1)

Can anybody see why this is?

Thanks in advance

Alex

Are you using VBA?

I'm not entirely sure I follow... but

B2: =MOD(FLOOR(A2+"00:10","00:05"),1)

Where A2 holds time to which 10 minutes are added, the prior 5 minute point will be returned... the MOD will handle the midnight issue... ie if A2 is 23:56 B1 would return 00:05

It's still difficult for me at least to visualise your setup... if you're trying to find the last 5 minute period for NOW + ten minutes then:

=FLOOR(NOW()+"00:10","00:05")

(NOW() is a DateTime value so you merely need to add ten minutes to it and adjust thereafter - the result of which will equally be a DateTime value, the Day portion will adjust as it crosses midnight)

post a sample file... I can't comprehend how you're getting 13th May based on NOW() + 10 mins.

No doubt I sound like a bore but can you explain each cell - I can't make head nor tail of it I'm afraid...

Re:
G5: =ROUND(FLOOR(NOW()+"00:10","00:05"),0)

By Rounding to 0 decimals you are dispensing with Time altogether and using only a Day value. In XL dates are Integers and Time is Decimal... ie Noon on 11th May = 39944.5
(the above value for today noon assumes 1904 Date System in Use)

If you round to nearest whole number (ie 0) you dispense with time... going back to your original point re: 13th May ... if the time portion of the value pre-rounding was >= noon you will end up rounding up to the next day given 0.5 (noon) will round to 1 where 1 equates to 1 entire day... if the time were < noon then it would round down to 00:00 of the same day.

Hi,

the reason i used round is because i only need to get the day to use. Try using it without the round in there, it goes crazy!

The original code wasn't written by me (i just have to attempt to fix the error)
so i'm not 100% on it either

I'm not sure about the reference to '<=0.999988426' but im assuming it denotes midnight of something along those lines. The other logical test in the formula i have adjusted - it is simply a test to ensure that the update time is in the past. If both these are true we add one day and use this as our day value in the second formula.

Hope this clears it up a bit

edit : 0.999988426 = (11:59:59 P.M.)

As advised don't use ROUND use INT to extract the date of the datetime value given the former will actually increase the day by 1 whenever the time portion of the datetime value is greater than or equal to noon.

Consider: X is 39944.5 (11th May Noon)

ROUND(X,0) --> 39945 --> 12th May
INT(X) --> 39944 --> 11th May

I'm afraid I'm going to have to bow out as I'm finding it impossible to follow what it is you're doing / trying to do. Perhaps a fresh set of eyes will help.

Maybe =CEILING(A1+"0:05", "0:05")

I don't:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Attached ...

You're welcome. Would you please mark the thread as Solved?