Adding 10 minutes to current time - issue at midnight

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

no - these are embedded within cells

**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()