Rounding time to nearest previous interval.

[Razorsharp]

Hello Everyone,

I am attempting to rounddown the time to the nearest previous interval.
I have some data in excel. It logs the start time of a bar. Eg the 16:00 bar begins at 16:00 and completes at 16:00:59
Now when the time is at 17:20 I would like excel to look for the time 16:00 (starting time of completed bar) and get the relevant values for it.

I had been using the roundown function in this format

=(ROUNDDOWN((A2*1440)/15,0)*15)/1440 , here rounding the 1m down to the nearest 15m bar. But it points only to the nearest 15m interval, I need it to point at the previous one. Currently at 17:18 it points to 17:15, at 17:18 I would like it to point to 17:00.


Any help would be greatly appreciated.

Thanking you all in advance.

Razor

 

[Razorsharp]

BTW, I couldn't work out how to edit the message. Is there a way that I can deal with the weekend issue here? for example on Monday the previous interval it will look at is Sunday, but there is no data for Sunday, only the Friday before.

Thank you

 

[tyija1995]

Hey, you can only edit a post within the first 10 minutes of writing it.

So you need to round down to the previous hour?

=(ROUNDDOWN((A2*1440)/60,0)*60)/1440

This will drop XX:XX down to XX:00

 

[Razorsharp]

Thank you problem solved.
Much appreciated.

 

[FormR]

Hi, welcome to the forum!

FWIW - you can also achieve the same results with:

=FLOOR(A2,"01:00")