+ Reply to Thread
Results 1 to 7 of 7

Date in cell in odd format (Mon Jun 20 10:41:45 GMT 2022)

  1. #1
    Registered User
    Join Date
    04-27-2022
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    14

    Date in cell in odd format (Mon Jun 20 10:41:45 GMT 2022)

    I am trying to get the Excel sheet to calculate the age of a defect (how many days it has been open) but the extract from the Octane software has this date format: Mon Jun 20 10:41:45 GMT 2022.

    I've never used VBA but I fear this may be a format that requires that sort of thing. Is there any way around this or is VBA the only thing and if so is it something a novice can do? See sheet attached. Please refer to the "Octane Extract Here" worksheet.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,815

    Re: Date in cell in odd format (Mon Jun 20 10:41:45 GMT 2022)

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    formatted as time and copied down.

    It's just text representing a date and time.

  3. #3
    Registered User
    Join Date
    04-27-2022
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Date in cell in odd format (Mon Jun 20 10:41:45 GMT 2022)

    When I try this on column M (Creation) it doesn't like the formula. I changed the letter from B to M in each case. Do you know why?

    Thank you

  4. #4
    Registered User
    Join Date
    04-27-2022
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Date in cell in odd format (Mon Jun 20 10:41:45 GMT 2022)

    I've tried it and it works on B (but not M - see comment above) but the array doesn't pull through the dates to other sheets.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,815

    Re: Date in cell in odd format (Mon Jun 20 10:41:45 GMT 2022)

    Works for me:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-27-2022
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Date in cell in odd format (Mon Jun 20 10:41:45 GMT 2022)

    It works! Thank you. I'm just comparing yours to mine to see why my changes didn't work.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,815

    Re: Date in cell in odd format (Mon Jun 20 10:41:45 GMT 2022)

    You're welcome. Thanks for the rep.


    It'll be something silly. Mistakes we all make

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Extract Rows & Prepare specific format 2022
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-03-2022, 03:52 PM
  2. Day of Week and date Tue - (2/3/2022)
    By Wskip49 in forum Excel General
    Replies: 10
    Last Post: 02-22-2022, 07:45 AM
  3. [SOLVED] How do I create formula for "All 2022 Mondays" as Column headers, in date format?
    By Zenmusicman in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-04-2022, 05:51 PM
  4. Want to covert date format from 13.01.2022 00:00:58.799 this to this 1/13/2022 0:00
    By Ali54121 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-14-2022, 01:24 AM
  5. Change format to date format if cell populated with a date
    By Lbelden78 in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 07-22-2019, 08:26 AM
  6. [SOLVED] Date format in cell differs from date format in formula bar
    By aziliza in forum Excel General
    Replies: 8
    Last Post: 08-23-2016, 01:12 AM
  7. Replies: 1
    Last Post: 11-01-2012, 10:37 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1