Question

For the country (Spain) I'm working in, the usual format for entering dates is dd/mm/aaaa. I have a form with two dates, one for planned delivery and another for actual, and need to calculate the difference, in days, between the two. They are both defined as text in the form. If I could use the US mm/dd/aaaa date format, there would be no problem, I could use the Spreadsheet TIME AND TIMEDIFF functions, but that format would originate confusion for the user. I was hoping to find some Spreadsheet funcion, some kind of Substring or Mid, that I could use in order to a temporary format change from dd/mm/aaaa to mm/dd/aaaa, but I can't find anything appropriate. Thanks in advance for ideas/workarounds.

Environment

-- RalphEllison - 11 Jan 2007

Answer

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.

I know, the dd/mm/yyyy date in the USA with mid-endian format is about as confusing as it can get. Nevertheless, there is help in converting the format.

You can do that by setting variables and $REPLACE(). Assuming a format with fixed width (e.g. zero padded days and month) you can do this: (view raw to see how)

• Set dd/mm/yyyy date: 31/12/2006
• Set dd: 31
• Set mm: 12
• Set yyyy: 2006
• Set mm/dd/yyyy format: 12/31/2006

You can combine that into one long formula:

• Set dd/mm/yyyy date: 31/12/2006
• Set mm/dd/yyyy format: 12/31/2006

If you want to support also formats that have no zero padding such as 5/3/2006, you can use a $FIND() to find the position of the slash.

• Set dd/mm/yyyy date: 5/12/2006
• Set dd: 5
• Set mm: 12
• Set yyyy: 2006
• Set mm/dd/yyyy format: 12/5/2006

Also this you can combine that into one long formula:

• Set dd/mm/yyyy date: 5/12/2006
• Set mm/dd/yyyy format: 12/5/2006

The result can be used in $TIME() and $TIMEDIFF().

If you want to re-use the conversion formula you can store the formula in a variable using $NOEXEC() and use it with an $EXEC(). See doc at SpreadSheetPlugin

-- PeterThoeny - 11 Jan 2007

Thanks Peter, very clear. I think I need to upgrade to use EXEC, but no great problem.

-- RalphEllison - 12 Jan 2007