Date and Time Incrementing

[dwgnome]

Looking to automate daily worksheets by having the date/time columns automatically fill in based on drop downs for Month and Year and while the Day is retrieved from sheet names (numbers 01 through 31). Although I think I have the date part working, I am trying to get the hour as well. When done, the date/time should be a number:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This is what the table on each worksheet (day) should look like when formatted as "hh:mm".<o></o>
<o><TABLE style="WIDTH: 114pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=189 border=0 x:str><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=113><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=76><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #9999ff 1pt solid; BORDER-LEFT: #9999ff 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #9999ff 2pt double; HEIGHT: 15pt; BACKGROUND-COLOR: #ccffcc" width=113 height=25>Time</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #9999ff 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 46pt; BORDER-BOTTOM: #9999ff 2pt double; BACKGROUND-COLOR: #ccffcc" width=76 x:str="Data ">Data </TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl67 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362">00:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff 0.5pt solid; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.041666666664">01:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.083333333336">02:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.125">03:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.166666666664">04:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.208333333336">05:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.25">06:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.291666666664">07:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.333333333336">08:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.375">09:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.416666666664">10:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.458333333336">11:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.5">12:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.541666666664">13:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.583333333336">14:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.625">15:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.666666666664">16:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.708333333336">17:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.75">18:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.791666666664">19:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.833333333336">20:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.875">21:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl69 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #9999ff 0.5pt solid; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.916666666664">22:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=23><TD class=xl70 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.1pt; BACKGROUND-COLOR: transparent" height=23 x:num="40362.958333333336">23:00</TD><TD class=xl68 style="BORDER-RIGHT: #9999ff 0.5pt solid; BORDER-TOP: #9999ff; BORDER-LEFT: #9999ff; BORDER-BOTTOM: #9999ff 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""></TD></TR></TBODY></TABLE></o>

If you highlight the 00:00 which is at Cell D6, you would actually see 7/3/2010 12:00:00 AM. So come to 12:00 through 23:00, there should be a PM at the end instead of AM. <o></o>
<o></o>
Since the workbook is dedicated to solely one month and year, I should only need to set up the month and year for the entire workbook only once and have all the other sheets refer to the drop downs on the first worksheet named 01. Once set up, is there a way to copy down from 00:00 and have the hour increment by one.<o></o>
<o></o>
This is what I have so far:<o></o>
<o></o>
Cell G38: Drop down list showing January, February, etc.<o></o>
Cell H38: =VALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)) to obtain the day such as 01, 02 ...31
Cell I38: Drop down list showing 2009,2010,2011 etc.<o></o>
<o></o>
In D6: =DATE(I38,MONTH(G38&"-"&I38),H38) which is a true number. For July 3, 2010, the cell when formatted as m/d/yyyy h:mm AM/PM shows 7/3/2010 12:00 AM but when I tried to use autofill down the value stayed the same. How can I get the hour to increment by one?<o></o>
<o></o>
I tried appending &ROWS($D$6:D6)&":00" to the formula in D6 and although it seems to add the hours in increments of 1-hour, the date portion remains serial format the result is no longer a number. <o></o>

Any assistance is appreciated.

 

[DonkeyOte]

Perhaps

D6:
=DATE($I$38,MONTH(1&$G$38),$H$38)+(ROWS(D$6:D6)-1)/24
copied down

 

[dwgnome]

That solved the issue.

Thank you DonkeyOte.