Solved

How do I format a date and time in Excel VBA ?

Posted on 2013-12-18
7
527 Views
Last Modified: 2013-12-19
Hi,

I have an Excel worksheet with date/time combinations held as 3 separate cells in the following formats:

Cell 1 (Date):             dd-mm-yyyy    (eg 24-Dec-2013)
Cell 2 (Time):             #n.nn     (eg 2.30)
Cell 3 (AM/PM):         xx      (eg  AM)

Using the data in these 3 cells I want to create a VBA function which will output a string in the following format:

[dddd] [dd] [mmmm] [yyyy] [hh.mm][am/pm]

For example,  Tuesday 24 December 2013 2.30pm

Any ideas ?

Thanks
Toco
0
Comment
Question by:Tocogroup
[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
  • 3
  • 3
7 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 39728561
Hi

Are the cells values dates/time or text?

Regards
0
 

Author Comment

by:Tocogroup
ID: 39728567
Hi

The cells values are formatted as follows:

Date: Date (dd-mm-yyyy)
Time: Number (to 2 decimal places)
AM/PM: General
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39728584
Hi

pls try

Function fFormatDateComplete(dt As Date, time As Double, AM_PM As String) As String
    fFormatDateComplete = Format(dt + TimeValue(Replace(Format(time, "0.00"), ".", ":") & " " & AM_PM), "dddd dd MMMM yyyy hh.mm am/pm")
End Function

Open in new window

Regards
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39728641
Do you really need VBA for some specific reason rather than a regular formula?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 39728645
Hi,

the formula would be

=TEXT(F4+TIMEVALUE(SUBSTITUTE(TEXT(G4;"0.00"),".",":")&" "&H4 ), "dddd dd MMMM yyyy hh.mm am/pm")

Open in new window

Regards
0
 

Author Comment

by:Tocogroup
ID: 39728677
Hi Rorya,

I'm using this expanded date and time value in a user form, and only once, so I don't want to create another column on my worksheet just for this one instance.
0
 

Author Closing Comment

by:Tocogroup
ID: 39728680
That's perfect. Thanks very much.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

622 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