Solved

Formatting Dates and Times in Excel

Posted on 2013-10-23
4
148 Views
Last Modified: 2013-12-01
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
Comment
Question by:greeneel
4 Comments
 
LVL 35

Assisted Solution

by:Kimputer
Kimputer earned 125 total points
Comment Utility
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
 
LVL 33

Assisted Solution

by:Norie
Norie earned 125 total points
Comment Utility
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
 
LVL 10

Assisted Solution

by:mark_harris231
mark_harris231 earned 125 total points
Comment Utility
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
 
LVL 11

Accepted Solution

by:
Murfur earned 125 total points
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now