Consulting

Results 1 to 6 of 6

Thread: Date Problem showing as 42463 ?

  1. #1
    VBAX Regular
    Joined
    Jun 2016
    Posts
    17
    Location

    Date Problem showing as 42463 ?

    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

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Use a Custom Number format of "dddd, d mmmm yyyy"
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Try:

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

    where A1 contain your date.

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,830
    adapt the cell's numberformat.

  5. #5
    VBAX Regular
    Joined
    Jun 2016
    Posts
    17
    Location
    Thank you work fine and I have also adapted that code in another worksheet
    Appreciate all you guys help
    Graham

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,171
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •