Adding 10 minutes to current time - issue at midnight

**DonkeyOte** · 05-11-2009, 06:38 AM

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

**thorne_** · 05-11-2009, 06:53 AM

thanks for your reply, but it wont return the correct date-time will it?

eg using todays date (11 may) at 23.51 it should return '2009-May-12 00:00:00'

and does but at 23.55 it returns '2009-May-11 00:00:00'

which is a problem

**DonkeyOte** · 05-11-2009, 07:20 AM

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)

**thorne_** · 05-11-2009, 07:42 AM

again thanks for your reply, that works for 23.55 - 00.00 but before that (from 11.50) it returns one day too much

eg @ 23.51 it return 2009-May-13 00.00.00

**DonkeyOte** · 05-11-2009, 07:45 AM

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

**thorne_** · 05-11-2009, 08:12 AM

hi,

I attached a sample - the left values are yours, the right values are what is there currently.

i replaced several parameters with constants and made the updatetime = now()

**DonkeyOte** · 05-11-2009, 08:22 AM

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.