PDA

View Full Version : [SOLVED] Date Problem showing as 42463 ?



Feebles
06-11-2016, 05:55 AM
I get that in G1 from a date list i created in Lists!range name as selectdate
how can I get H1 to format it back to a date as

Sunday, 3 April 2016
appreciate any help here as usual
Regards
Graham

mdmackillop
06-11-2016, 07:02 AM
Use a Custom Number format of "dddd, d mmmm yyyy"

Tom Jones
06-11-2016, 07:17 AM
Try:

=TEXT(A1,"dddd dd mmmm yyyy")

where A1 contain your date.

snb
06-11-2016, 07:30 AM
adapt the cell's numberformat.

Feebles
06-11-2016, 03:32 PM
Thank you work fine and I have also adapted that code in another worksheet
Appreciate all you guys help
Graham

SamT
06-11-2016, 08:58 PM
Try:

=TEXT(A1,"dddd dd mmmm yyyy")

where A1 contain your date.
Excel may not recognise TEXT as a date. 42463 (3 Apr 16) is the way Excel stores and manipulates dates. 42463.5 Would be Displayed as 03/04/15 12:00 PM.

Try this:
Enter "=NOW()" in A1
Enter "=A1" in B1

Format A1 as Date and Time
Format B1 as an 11 decimal place number

Enter 42400 in D1
Enter 0.57395638 in E1
Enter "=D1+E1" in F1

Format all three as Date and Time