| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
| convert from percentage of time to time using complex formula in . | Excel Worksheet Functions | |||
| Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
| template or formula for start time -finish time -total hours ple | New Users to Excel | |||
| How do I set up a formula on a time sheet to calculate time in 1/. | Excel Discussion (Misc queries) | |||
Linear Mode
