Help with time formula
 

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.

Help with time formula
 

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" wrote:

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.

Help with time formula
 

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.

"Joel" wrote:

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" wrote:

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.

Help with time formula
 

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

"Josh W" wrote:

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.

Help with time formula
 

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


"Josh W" wrote:

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

"Josh W" wrote:

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.

Help with time formula
 

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


"Josh W" wrote in message
...
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.

Help with time formula
 

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

--
Biff
Microsoft Excel MVP


"Josh W" wrote in message
...
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.

Help with time formula
 

This formula is a little bit shorter...

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Josh W" wrote in message
...
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.

Help with time formula
 

And this formula is shorter yet...

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
This formula is a little bit shorter...

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Josh W" wrote in message
...
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.

Help with time formula
 

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.
"Josh W" wrote:

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.

Help with time formula
 

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


"Josh W" wrote in message
...
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.
"Josh W" wrote:

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.

Help with time formula
 

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
And this formula is shorter yet...

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
This formula is a little bit shorter...

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

Rick


"Rick Rothstein (MVP - VB)" wrote
in message ...
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


"Josh W" wrote in message
...
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.

Help with time formula
 

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

"Rick Rothstein (MVP - VB)" wrote:

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


"Josh W" wrote in message
...
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.
"Josh W" wrote:

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.