Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts
    A colleague of mine in the USA has sent me an Excel spreadsheet with some dates. He has entered the dates into Excel in the format mm/dd/yyyy. I am based in the UK with my PC regional settings set to English UK. When I open the sheet some of the cells display '#VALUE!' and when clicking in one of the date cells, it says '=DATEVALUE("MM/DD/YYYY")'. If the date in the parenthesis is understood to be a UK format date, it displays the value in the cell i.e. DD/MM/YYYY which is incorrect. For example =DATEVALUE("02/05/09") is being interpretted as 2nd May 2009 rather than 5th February 2009. See attached file for examples.

    I've tried using the Format Cells function and choosing my date format but it makes no difference. What can I do from my end to get rid of the datevalues and switch the dates to the UK format of DD/MM/YYYY? Thanks.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm sorry for your colleague, but this is a totally ridiculous way to enter dates in a spreadsheet. There is no need whatsoever to use DATEVALUE here.
    Your colleague should redo the sheet, and simply enter the dates directly instead of using a formula, for example 02/05/09 instead of =DATEVALUE("02/05/09"), for the 5th of February 2008. The cells should be formatted with the first date format in Format | Cells. This format will automatically adjust itself to the Windows settings, so your colleague would see 02/05/09 (US format), while you would see 05/02/09 (UK format) and I would see 05-02-2009 (Dutch format)

  3. #3
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorry, you misunderstand. My colleague IS directly typing the date in his end directly as the date.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='bel_ami_boy' post='763347' date='04-Mar-2009 15:50']Sorry, you misunderstand. My colleague IS directly typing the date in his end directly as the date.[/quote]
    How did the formulas get there? Did you create them?

  5. #5
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Good question. No I didn't create them. I received the spreadsheet exactly as you can see it. For the purposes of this forum I've just removed the rest of the columns we have no interest in. It is something Excel is doing itself.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Excel doesn't create formulas spontaneously. They have either been entered by someone, or they have been created by Visual Basic code. The workbook doesn't contain any code, though...

  7. #7
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Very odd! Is there any way I can extract the date element out of the formula?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='bel_ami_boy' post='763358' date='04-Mar-2009 16:20']Very odd! Is there any way I can extract the date element out of the formula?[/quote]
    Ask your colleague to send a normal spreadsheet.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could do the following:
    - Set your Windows to use US date format.
    - Open the workbook. You shouldn't see #VALUE now.
    - Select the cells with the DATEVALUE formulas.
    - Copy them.
    - Select Edit | Paste Special...
    - Select the Values option and click OK.
    - Save and close the workbook.
    - Set your Windows date format back to UK date format.
    - Reopen the workbook.

  10. #10
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='bel_ami_boy' post='763358' date='04-Mar-2009 10:20']Very odd! Is there any way I can extract the date element out of the formula?[/quote]

    Copy the columns to another location. The cells should recalculate to show the dates.
    Copy the recalculated dates and paste them back to their original location using Paste Special... Values.

    Send email to your source asking them "What were you thinking?"



    Ahh Crum... Forgot about the setting of your date formatting within Windows itself

  11. #11
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts
    My colleague has just clarified that some of the data on the spreadsheet is extracted from his stock system. Crucially the date fields I'm having problems with are actually coming from his system. When he views the Excel file, he just sees the date in the cell (in the format MM/DD/YYYY). I don't think this helps us though does it?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='bel_ami_boy' post='763369' date='04-Mar-2009 16:51']My colleague has just clarified that some of the data on the spreadsheet is extracted from his stock system. Crucially the date fields I'm having problems with are actually coming from his system. When he views the Excel file, he just sees the date in the cell (in the format MM/DD/YYYY). I don't think this helps us though does it?[/quote]
    I'm inclined to think he's mistaken, but that won't help. My previous reply shows how you can "repair" the worksheet.

  13. #13
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you.

Posting Permissions

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