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
Solved

Formatting Dates and Times in Excel

Posted on 2013-10-23
4
152 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
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 33

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

861 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