Hi, I need to create a spreadsheet with Month, Day, and Time in Col A, B, and C respectively. My first time point is 5/17/2013 at 2200. How can I enter a formula that will populate the time in half hour increments and loop back to "0000" after "2330"? Also, I need the date alongside each time point. I entered the first day (B2) and time (C2) manually and applied this formula to B3: =IF(C3>0,B2,B2+1)
A B C
1 Month Day Time
2 5 17 2200
3 5 17 2230
4 5 17 2300
5 5 17 2330
6 5 17 0000
7 5 18 0030
8 5 18 0100
This works well for all time points >0, but when the formula encounters the time point "0000", it enters the previous date for that time point, when it is actually the first record for the subsequent day. If there was a way to use an advanced "find and replace" rule I could fix this by creating this rule: FIND where C (time point)="0000", REPLACE adjacent cell in column B(day) with the cell below. So in data above, the rule would FIND C6 and REPLACE B6 with B7.
Any advice would be appreciated, I tried to make this question thorough but concise. Thank you!!