Help with time formula

[Josh W]

I am trying to create a time formula in increments of 15 minutes then 5
minutes then again 15 minutes then 5 minutes etc as follows::
cell A1 08:00
cell A2 08:15
cell A3 08:20
cell A4 08:35
cell A5 08:40
cell A6 08:55
Cell A7 09:10
How do I do this? Thanks, Excel 2003.

 

[Joel]

One way is to look at the row number if even then add 15 if odd add 5

Put this in A2 and copy down column. form cells as HH:MM

=IF(MOD(ROW($A2),2)=0,($A1+TIMEVALUE("0:15")),($A1+TIMEVALUE("0:05")))

 

[Josh W]

Thanks Joel, I just copied your formual and it works fantastic! Though I
didn't understand what I was doing and I would love to.

 

[Josh W]

Correction! A7 has to be 09:00 (the formula is 15 min+5min+15min+5min etc.

 

[Joel]

In my Excel 2003 it works fine. Makeusre it is really a cell in row 7. do
you have any hidden rows.

The formula is pretty simple. It take the row number and divides by 2. If
it is an odd row it adds 5 minutes, otherwise, it add 15 minutes. I used
timevalue to convert 00:05 and 00:15 to an excel serial time.



8:00
8:15
8:20
8:35
8:40
8:55
9:00
9:15
9:20
9:35
9:40
9:55
10:00
10:15
10:20
10:35
10:40
10:55
11:00
11:15
11:20
11:35
11:40
11:55
12:00
12:15
12:20
12:35

 

[Rick Rothstein \(MVP - VB\)]

Should the value in A7 be 09:00 instead of the value 09:10 as you show? If
so, put 8:00 in A1, put this formula in A2...

=A1+TIME(0,15-10*(RIGHT(MINUTE(A1))="5"),0)

and copy it down. Make sure you Format Cells in the column as hh:mm.

Rick

 

[T. Valko]

Here's another one...

A1 = 8:00

Enter this formula in A2 and copy down as needed:

=A1+TIME(0,IF(MOD(ROWS(A$2:A2),2),15,5),0)

Format as [h]:mm

 

[Rick Rothstein \(MVP - VB\)]

This formula is a little bit shorter...

=A1+TIME(0,5+10*(MOD(1440*A1,10)=0),0)

Rick

 

[Rick Rothstein \(MVP - VB\)]

And this formula is shorter yet...

=A1+(5+10*(MOD(1440*A1,10)=0))/1440

Rick

 

[Josh W]

All the formulas worked. Only, when I get to 13:00 the format suddenly turns
into 0:15
0:05
0:15
0:05
and so on. How do I overcome this? I want the formula for 24 hours a day.
Thanks.

 

[Rick Rothstein \(MVP - VB\)]

First off, the two shorter formulas I posted appear not to work correctly as
the time progresses, so ignore them. However, the first formula I posted
does appear to work correctly and, if you Custom Format the column with
hh:mm, it seems to work correctly throughout the full 24-hour range and
beyond. Give it a try. Here it is again so you don't have to look back for
it...

=C21+TIME(0,15-10*(RIGHT(MINUTE(C21))="5"),0)

Rick

 

[Rick Rothstein \(MVP - VB\)]

Only the first formula I posted appears to work correctly... the two shorter
formulas appear to fail at 11:20.

Rick

 

[Josh W]

I have now rechecked your first formula and custom formatted the cells, it
works perfectly for the full 24 hours! Great, thanks so much.