Solved

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

Posted on 2013-12-18
7
517 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 51

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 51

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
Independent Software Vendors: 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 51

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
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 two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

734 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