Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating/incrementing dates 35039 times
I am putting together a template for our reports and need to have every date
of the year repeat itself 95 times (1 time for every 15 minutes in a day), thenincrement to the next day. In the end column A should start at 01-jan-07 column B: 0:00:00 and repeat until 01-jan-07, 23:45:00 then start at 02-jan-07, 0:00:00. and so on until 31-dec-07, 23:45:00. We have the times in column B already in there (from the 2006 template) but need to convert the 2006 dates to 2007. any ideas? The person who did this in the past is no longer with the company so I do not know how he managed it. currently they are just straight values so it will take a long time to switch every year to 07. Thank you! Daiv. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating/incrementing dates 35039 times
How about formatting under date as dd/3mm/yyyy hh:mm:ss
enter 1/1/07 00:00:00, then 1/1/07 00:15:00, then drag down? "Daiv" wrote: I am putting together a template for our reports and need to have every date of the year repeat itself 95 times (1 time for every 15 minutes in a day), thenincrement to the next day. In the end column A should start at 01-jan-07 column B: 0:00:00 and repeat until 01-jan-07, 23:45:00 then start at 02-jan-07, 0:00:00. and so on until 31-dec-07, 23:45:00. We have the times in column B already in there (from the 2006 template) but need to convert the 2006 dates to 2007. any ideas? The person who did this in the past is no longer with the company so I do not know how he managed it. currently they are just straight values so it will take a long time to switch every year to 07. Thank you! Daiv. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating/incrementing dates 35039 times
In A1:
2-jan-2006 (if that is your date format) In A2: =A1+(1/24/4) Format as date including time, fill down as far as you need. It will increase dates (even years, in Excel2007) when needed automatically -- Kind regards, Niek Otten Microsoft MVP - Excel "Daiv" wrote in message ... |I am putting together a template for our reports and need to have every date | of the year repeat itself 95 times (1 time for every 15 minutes in a day), | thenincrement to the next day. In the end column A should start at 01-jan-07 | column B: 0:00:00 and repeat until 01-jan-07, 23:45:00 then start at | 02-jan-07, 0:00:00. and so on until 31-dec-07, 23:45:00. | | We have the times in column B already in there (from the 2006 template) but | need to convert the 2006 dates to 2007. any ideas? The person who did this | in the past is no longer with the company so I do not know how he managed it. | currently they are just straight values so it will take a long time to | switch every year to 07. | | Thank you! | Daiv. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating/incrementing dates 35039 times
That tries to show the time as well in that cell which we don't want.
Otherwise, it did seem to increment the dates like I wanted. How can i use that and not have it show the time? "Sean Timmons" wrote: How about formatting under date as dd/3mm/yyyy hh:mm:ss enter 1/1/07 00:00:00, then 1/1/07 00:15:00, then drag down? "Daiv" wrote: I am putting together a template for our reports and need to have every date of the year repeat itself 95 times (1 time for every 15 minutes in a day), thenincrement to the next day. In the end column A should start at 01-jan-07 column B: 0:00:00 and repeat until 01-jan-07, 23:45:00 then start at 02-jan-07, 0:00:00. and so on until 31-dec-07, 23:45:00. We have the times in column B already in there (from the 2006 template) but need to convert the 2006 dates to 2007. any ideas? The person who did this in the past is no longer with the company so I do not know how he managed it. currently they are just straight values so it will take a long time to switch every year to 07. Thank you! Daiv. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating/incrementing dates 35039 times
Once you have it all filled out, highlight the column again and format as
dd/mm/yyyy "Daiv" wrote: That tries to show the time as well in that cell which we don't want. Otherwise, it did seem to increment the dates like I wanted. How can i use that and not have it show the time? "Sean Timmons" wrote: How about formatting under date as dd/3mm/yyyy hh:mm:ss enter 1/1/07 00:00:00, then 1/1/07 00:15:00, then drag down? "Daiv" wrote: I am putting together a template for our reports and need to have every date of the year repeat itself 95 times (1 time for every 15 minutes in a day), thenincrement to the next day. In the end column A should start at 01-jan-07 column B: 0:00:00 and repeat until 01-jan-07, 23:45:00 then start at 02-jan-07, 0:00:00. and so on until 31-dec-07, 23:45:00. We have the times in column B already in there (from the 2006 template) but need to convert the 2006 dates to 2007. any ideas? The person who did this in the past is no longer with the company so I do not know how he managed it. currently they are just straight values so it will take a long time to switch every year to 07. Thank you! Daiv. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating/incrementing dates 35039 times
I after thought to use your technique and then reformat the cells after to
the Date only format and it worked great. thanks! Daiv. "Sean Timmons" wrote: How about formatting under date as dd/3mm/yyyy hh:mm:ss enter 1/1/07 00:00:00, then 1/1/07 00:15:00, then drag down? "Daiv" wrote: I am putting together a template for our reports and need to have every date of the year repeat itself 95 times (1 time for every 15 minutes in a day), thenincrement to the next day. In the end column A should start at 01-jan-07 column B: 0:00:00 and repeat until 01-jan-07, 23:45:00 then start at 02-jan-07, 0:00:00. and so on until 31-dec-07, 23:45:00. We have the times in column B already in there (from the 2006 template) but need to convert the 2006 dates to 2007. any ideas? The person who did this in the past is no longer with the company so I do not know how he managed it. currently they are just straight values so it will take a long time to switch every year to 07. Thank you! Daiv. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
negative dates or times are displayed as # # # # | New Users to Excel | |||
Calculate number of hours between dates and times excluding Weekends | Excel Discussion (Misc queries) | |||
Pivot table dates not times | Excel Worksheet Functions | |||
Autoformat dates and times | Excel Discussion (Misc queries) | |||
Times and Dates | Excel Discussion (Misc queries) |