change time stamp to 15 minute intervals

[Rebekahand]

Hello!

I am looking for a way to auto change a time stamp or a entered time into a 15 minute interval

My job has me bill in 15 minute units so i document in excel as i go. It would be a lot easier if i could put the time "14:32" in and it auto changed it to "14:30-14:45"

Any formulas for this?

 

[Andrew Fergus]

Hello and welcome to MrExcel!

Excel can't change the value contained in an input cell using a formula, instead we use a helper cell to work on what was input.

Before we start a quick explanation on how Excel stores time values. Excel stores time as a value between 0 and 1 and then displays that value as a time. For instance 0.5 is midday, 0.25 is 6am and 0.75 is 6pm etc. There are 1440 minutes per day and 96 quarter hours per day (this knowledge will come in handy in about 30 seconds). Assuming you have entered a time value such as 14:32 into cell A2, then the following formulas will give what you want:

Cell B2: =ROUNDDOWN(A2*96,0)/96
This gives you the start time for the quarter hour. Format as time.

An issue with your request is it is unclear if 14:45 belongs in the 14:30-14:45 bracket, or the 14:45-15:00 bracket. Instead should the bracket be 14:30-14:44? I am assuming that is the case with this formula:
Cell C2: =ROUNDUP((A2+1/1440)*96,0)/96-1/1440
This gives you the end time of the quarter hour. Format as time.

If however you want to show 14:45 instead of 14:44 as the end time, then this formula in C2 will give the answer you want:
or Cell C2: =ROUNDUP((A2+1/1440)*96,0)/96

These values will be in 2 separate cells (B2 & C2) but can be combined using another formula in in cell D2:
=TEXT(B2,"hh:mm") & "-" & TEXT(C2,"hh:mm")

You could combine all 3 formulas into one if you are comfortable doing that.

I trust that helps

 

[Cubist]

Try

=TEXT(ROUND(A1*24*4,0)/(24*4),"hh:mm") & "-" & TEXT(ROUND((A1+TIME(0,15,0))*24*4,0)/(24*4),"hh:mm")