Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

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..
0
Eaddy Barnes
Asked:
Eaddy Barnes
4 Solutions
 
KimputerCommented:
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
 
NorieCommented:
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_harris231Commented:
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
 
MurfurFull 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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now