+ Reply to Thread
Results 1 to 15 of 15

Crazy Date format problems

  1. #1
    Registered User
    Join Date
    03-21-2017
    Location
    Florida
    MS-Off Ver
    2011
    Posts
    10

    Crazy Date format problems

    Hi,

    ** Excel Version 16.28 for Mac / iMac 27" (High Spec), running Mojave 10.14.6

    I'm having some issues with date formats from a workbook downloaded from a Govt/County site.

    The date as downloaded shows as two different date formats inside one column:

    03/17/2016 02:22:00 PM - formatted as General, and

    2/09/16 13:29 - formatted as Custom (d/mm/yy h:mm)

    The issue is that I want to change the formatting to read "14 March 2019" (or similar - but must have the month in letters to fit with the other worksheets I use). I've tried changing the whole column to one of the date formats available, but none of the cell info changes.

    Inside the Format window (Protection tab), the cells are locked, but unlocking them does nothing. I've also tried using the custom format option to write my own formats but again, nothing seems to be working.

    Any idea's greatfully received.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,448

    Re: Crazy Date format problems

    It is difficult to debug from this side of the internet. Many times, this sort of problem usually boils down to if the date string is being imported as a text string or if Excel is recognizing it as a date and converting it to a date/time serial number. If it is being imported as a number then Excel's number formatting should readily display the date in whatever format you desire (within the limits of what custom number formatting can do). If it is imported as a text string, then number formatting won't change anything, and you either need to figure out how to convert it to a number or manipulate it as a text string.

    If I had the worksheet in front of me, I would start debugging with something like an ISTEXT() function to see if the date is being imported as a text string or as a number.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    03-21-2017
    Location
    Florida
    MS-Off Ver
    2011
    Posts
    10

    Re: Crazy Date format problems

    Hi MrShorty,

    I presume there's no way to attach a sample file here?

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    2,968

    Re: Crazy Date format problems

    Hi there,

    Take a look at the attached workbook and see if it gets you moving in the right direction.

    Column B contains a mixture of date & time values using the two formats you described.

    Cell C2 contains the following formula:

    Please Login or Register  to view this content.
    which is copied downwards as far as is required, and the values in Column C are formatted as "d mmmm yyyy".


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-21-2017
    Location
    Florida
    MS-Off Ver
    2011
    Posts
    10

    Re: Crazy Date format problems

    Hi MrShorty / Greg M,

    OK let's try this - workbook (sample) attached.

    I'll try your suggestions first thing.

    Thanks for your replies and help.
    Attached Files Attached Files

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    2,968

    Re: Crazy Date format problems

    Hi again,

    Ok, I think that you may need to use "helper columns" (E, J, L, N) as shown in the attached workbook.

    The basic formula is entered in Cell E2 as:

    Please Login or Register  to view this content.
    Hope this helps - as before please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,448

    Re: Crazy Date format problems

    A couple of thoughts that I would toss in:

    1) Greg M's formula works for the date, and if the date is all you need to carry over into the serial number, then that is fine. If you need both date/time to carry over, then you will need an addition or variation on his formula that will carry time of day over as well. Since your "date as text" values are all consistently "mm/dd/yyyy hh:mm:ss AM") text strings (where the left 10 characters contain the date and the right 11 characters contain the time of day), and your dates and times are each fairly standard US formats (and I assume there will never be an international dd/mm/yyyy date in your data), I might use a combination of the DATEVALUE() and TIMEVALUE() functions. Still need an IF() function to test for if the date/time stamp is text or not, so something like =IF(ISNUMBER(D2),D2,DATEVALUE(LEFT(D2,10))+TIMEVALUE(RIGHT(D2,11))). Be sure to note all the conditions that go into making that work, because some of it (especially the DATEVALUE() function) is subject to the whims of your operating system's regional settings.

    2) I have a program that outputs a tab-delimited text file with date/time stamps in the same kind of text format. When I import those text files into Excel (using the Import External data from text command), Excel automatically recognizes all of the date/time stamps and does the conversion during the import. You have not said anything about how this data is created or how you are bringing it into Excel. I would be inclined to look at that process and see if I could do something different in the creation/importation process that would cause Excel to consistently and reliably recognize the date/time stamps and convert them to serial numbers upon import. That would eliminate the need for all of this post import processing.

  8. #8
    Registered User
    Join Date
    03-21-2017
    Location
    Florida
    MS-Off Ver
    2011
    Posts
    10

    Re: Crazy Date format problems

    Hi Greg M - exactly what I needed. The only update I applied was the custom date format - I mis-typed "March" in my first post - it should actually be a shortened date "Mar", so changed to "d mmm yyyy" and this works perfectly. Thanks so much - I'd never have been able to figure that formula out for myself.

    MrShorty - also thanks for your input & valuable information (especially the reminder about the regional settings as I've had issues with that previously) - also very helpful however I have no need (in this worksheet) for the time value in the cells, but it may be useful in the future.

    The Excel sheet is created by the Govt/County staff, and I download from an ftp site so I'm not sure if there may be a method to convert to the format I need when I first open the "new" workbook at the start of each month?

  9. #9
    Registered User
    Join Date
    03-21-2017
    Location
    Florida
    MS-Off Ver
    2011
    Posts
    10

    Re: Crazy Date format problems

    Hi Greg M - exactly what I needed. The only update I applied was the custom date format - I mis-typed "March" in my first post - it should actually be a shortened date "Mar", so changed to "d mmm yyyy" and this works perfectly. Thanks so much - I'd never have been able to figure that formula out for myself.

    MrShorty - also thanks for your input & valuable information (especially the reminder about the regional settings as I've had issues with that previously) - also very helpful however I have no need (in this worksheet) for the time value in the cells, but it may be useful in the future.

    The Excel sheet is created by the Govt/County staff, and I download from an ftp site so I'm not sure if there may be a method to convert to the format I need when I first open the "new" workbook at the start of each month?

  10. #10
    Registered User
    Join Date
    03-21-2017
    Location
    Florida
    MS-Off Ver
    2011
    Posts
    10

    Re: Crazy Date format problems

    Thanks to you both anyways. Very helpful indeed.
    Last edited by troyb728; 09-12-2019 at 08:01 PM.

  11. #11
    Registered User
    Join Date
    03-21-2017
    Location
    Florida
    MS-Off Ver
    2011
    Posts
    10

    Re: Crazy Date format problems

    Sorry guys, the page seemed to freeze for a while and so I wasn't able to reply. But it looks like each time I've hit the Reply button it's actually posted.

    How do I remove/delete posts?
    Last edited by troyb728; 09-12-2019 at 08:11 PM.

  12. #12
    Registered User
    Join Date
    03-21-2017
    Location
    Florida
    MS-Off Ver
    2011
    Posts
    10

    Re: Crazy Date format problems

    Hi Greg M - exactly what I needed. The only update I applied was the custom date format - I mis-typed "March" in my first post - it should actually be a shortened date "Mar", so changed to "d mmm yyyy" and this works perfectly. Thanks so much - I'd never have been able to figure that formula out for myself.

    MrShorty - also thanks for your input & valuable information (especially the reminder about the regional settings as I've had issues with that previously) - also very helpful however I have no need (in this worksheet) for the time value in the cells, but it may be useful in the future.

    The Excel sheet is created by the Govt/County staff, and I download from an ftp site so I'm not sure if there may be a method to convert to the format I need when I first open the "new" workbook at the start of each month?

  13. #13
    Registered User
    Join Date
    03-21-2017
    Location
    Florida
    MS-Off Ver
    2011
    Posts
    10

    Re: Crazy Date format problems

    Hi Greg M - exactly what I needed. The only update I applied was the custom date format - I mis-typed "March" in my first post - it should actually be a shortened date "Mar", so changed to "d mmm yyyy" and this works perfectly. Thanks so much - I'd never have been able to figure that formula out for myself.

    MrShorty - also thanks for your input & valuable information (especially the reminder about the regional settings as I've had issues with that previously) - also very helpful however I have no need (in this worksheet) for the time value in the cells, but it may be useful in the future.

    The Excel sheet is created by the Govt/County staff, and I download from an ftp site so I'm not sure if there may be a method to convert to the format I need when I first open the "new" workbook at the start of each month?

  14. #14
    Registered User
    Join Date
    03-21-2017
    Location
    Florida
    MS-Off Ver
    2011
    Posts
    10

    Re: Crazy Date format problems

    Thanks to you both anyways. Very helpful indeed.
    Last edited by troyb728; 09-12-2019 at 07:59 PM.

  15. #15
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    2,968

    Re: Crazy Date format problems

    Hi again,

    Many thanks for your feedback and also for the Reputation increase - much appreciated!

    You're welcome, glad I was able to help, and apologies for the late reply - I was away on holidays.

    Best regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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