Formatting Dates and Times in Excel

hello Experts,

I have an excel file whereas i need to generate dates and times sequentially if i copy down to other cells.
e.g in the attached file in cell A1 i have the date and time 10/25/2013 8:00 AM now what i want done is to be able to
copy down to cell A2 and get 10/25/2013 10:00 AM
copy down to cell A3 and get 10/25/2013 12:00 PM
copy down to cell A4 and get 10/25/2013 14:00 PM
copy down to cell A5 and get 10/25/2013 16:00 PM
copy down to cell A6 and get 10/25/2013 18:00 PM
copy down to cell A7 and get 10/25/2013 20:00 PM
copy down to cell A8 and get 10/25/2013 22:00 PM

Dates of the months starting from the 25th going on indefinitely with times from 10am to 10pm everyday..
LVL 11
Eaddy BarnesITAsked:
Who is Participating?
 
MurfurConnect With a Mentor Full Stack DeveloperCommented:
You can do it by this formula although in its current state it won't auto-increment the day after midnight:

1. set column format as CUSTOM mm/dd/yy hh:mm

2. put initial date/time in the first row i.e. 10/25/2013 10:00


3. add formula to row 2 of the same column:

=LEFT(R[-1]C, 10) & " " & TEXT(RIGHT(R[-1]C,5)+2*(1/24),"hh:mm")

Open in new window

4. increment downwards

0
 
KimputerConnect With a Mentor Commented:
Here's some sample code, you need to adjust a few things to your likings.
For instance, it always starts from cell A1 (please have a date already input here). Also, you need to input the amount of days, as "indefinitely" is quite dangerous (stuck loop, or excel column overload).

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("A:A").Select
    Selection.NumberFormat = "m/d/yy h:mm AM/PM"
    
    
    Dim celldate, expiredate As Date
    Dim days As Integer
    i = 1
    days = InputBox("how many days?")
    celldate = Format(Cells(1, 1).Value, "m/d/yy h:mm AM/PM")
    expiredate = DateAdd("d", days, celldate)
    
    Do Until celldate > expiredate

        If (Hour(celldate) >= 8) And (Hour(celldate) <= 20) Then
            celldate = DateAdd("h", 2, celldate)
            Cells(i + 1, 1).Value = celldate
            i = i + 1
        Else
            celldate = DateAdd("h", 2, celldate)
        End If
        
        
    Loop
    
    MsgBox "end"
    
End Sub

Open in new window

0
 
NorieConnect With a Mentor VBA ExpertCommented:
Try this, but be warned it does the entire column.
Option Explicit

Sub FillDatesAndTimes()
Dim rng As Range

    Set rng = Range("A1")
    
    rng.Value = CDate("25 Oct 2013 8:00 AM")
    
    While rng.Row < Rows.Count - 8
        rng.Resize(8).DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
            xlDay, Step:=TimeSerial(2, 0, 0), Trend:=False
        Set rng = rng.Offset(8)
        rng.Value = rng.Offset(-8).Value + 1
    Wend
    
    rng.EntireColumn.NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
    
End Sub

Open in new window

0
 
mark_harris231Connect With a Mentor Commented:
Optionally, to do this without code, simply define a custom format (m/d/yyyy h:mm AM/PM) and apply it to the column/cells, then enter your first two cell values, highlight and drag down as far as you need.  Excel will automatically increment the hours by 2 and change days.

10/25/2013 8:00 AM
10/25/2013 10:00 AM

Note that this won't work if you need the pattern to skip hours between 10PM - 10AM.  I wasn't sure if that was your intent since you led with 8AM.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.