DATEVALUE problem

Topic

New Reply

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.

Reply | Quote

Replies

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)

Reply | Quote

Sorry, you misunderstand. My colleague IS directly typing the date in his end directly as the date.

Reply | Quote

Sorry, you misunderstand. My colleague IS directly typing the date in his end directly as the date.

How did the formulas get there? Did you create them?

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

Very odd! Is there any way I can extract the date element out of the formula?

Reply | Quote

Very odd! Is there any way I can extract the date element out of the formula?

Ask your colleague to send a normal spreadsheet.

Reply | Quote

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.

Reply | Quote

Very odd! Is there any way I can extract the date element out of the formula?

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

Reply | Quote

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?

Reply | Quote

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?

I’m inclined to think he’s mistaken, but that won’t help. My previous reply shows how you can “repair” the worksheet.

Reply | Quote

Thank you.

Reply | Quote