Solved

Formatting Dates and Times in Excel

Posted on 2013-10-23
4
154 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:Eaddy Barnes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 35

Assisted Solution

by:Kimputer
Kimputer earned 125 total points
ID: 39593656
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 34

Assisted Solution

by:Norie
Norie earned 125 total points
ID: 39594126
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
ID: 39594476
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
ID: 39594725
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 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