Link to home
Start Free TrialLog in
Avatar of Tocogroup
TocogroupFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

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
Avatar of Rgonzo1971
Rgonzo1971
Hi

Are the cells values dates/time or text?

Regards
Avatar of Tocogroup
Tocogroup
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Hi

The cells values are formatted as follows:

Date: Date (dd-mm-yyyy)
Time: Number (to 2 decimal places)
AM/PM: General
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
Do you really need VBA for some specific reason rather than a regular formula?
Avatar of Rgonzo1971
Rgonzo1971
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
Avatar of Tocogroup
Tocogroup
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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.
Avatar of Tocogroup
Tocogroup
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

That's perfect. Thanks very much.