How to find difference between two dates when dates format DD/MM/YYYY.

[Yugaa2010]

Hi,

I am able to get the difference between two dates when dates are in mm/dd/yyyy but not when format as dd/mm/yyyy.

How to find difference between two dates format is DD/MM/YYYY.either formula or VBA.

At the same how can I change my Excel timezone currently for all showing as mm/dd/yyyy to dd/mm/yyyy. (Not by format cells Ctrl+1).


Thanks in advance .

 

[petertenthije]

You can use this formula to modify the dates:
=DATE(RIGHT(A1,4),LEFT(A1,2),LEFT(RIGHT(A1,7),2))


edited to change semicolons to commas to make the formula work on non-Dutch Excel versions.

 

[jasonb75]

The suggestion in post 2 will only work if the day of the date in question is on or after the 13th.

Days on or before the 12th will result in day and month being reversed, i.e. 1st March will be read as 3rd January. Because both are valid with your system settings, the right and left functions will look at the date serial instead of the actual date and give you an output of something like 12/08/3836.

The most reliable way to resolve the problem is to convert the dates in dd/mm/yyyy format to mm/dd/yyyy format using text to columns, by selecting the whole range of dates (one column at a time if multiple columns) then following the steps below on the excel ribbon.

Data tab > Text to columns > Next > Uncheck all boxes > Next > Select your correct date format from the dropdown (MDY) > Finish.

The 'Timezone' of excel is linked to your system settings, so to change it you would need to change the regional settings of your pc, doing this could create more problems than it fixes!

 

[HappierThan]

Congratulations 'jasonb75' great explanation and I learnt something also. My question would be to identify which format is in use for a date such as 03/01/2020?

Would temporarily formatting ALL dates to general give you which ones ARE NOT in your mm/dd/yyyy format?

These dates could then have the Text to Columns operation perhaps until they too showed a 5 digit number when formatted in General?

 

[jasonb75]

My question would be to identify which format is in use for a date such as 03/01/2020?

With imported data it would be reasonable to assume that all dates for a single data source are in the same format, so you would identify it based on dates where the day is on or after the 13th, if you don't have any such dates then it would probably mean asking the information from the originator.

Would temporarily formatting ALL dates to general give you which ones ARE NOT in your mm/dd/yyyy format?

It depends on the format of the import, if the dates are imported in text format, which is quite common then doing this would not show the serial numbers for valid dates until text to columns has been applied and converted them from text to numeric format.

That said, if the dates are numeric and you format them as serial numbers then your data will end up in a big mess. Using my earlier example, 1st March will be interpreted as 3rd Jan, if you run text to columns on this when it is formatted as date then it will be 'corrected' to match your settings (the date serial will be changed), if it was formatted as general to show the date serial then it would be evaluated as a number, not a date serial and remain unchanged.

These dates could then have the Text to Columns operation perhaps until they too showed a 5 digit number when formatted in General?

No, with what I've explained above, this would mean that when you run text to columns, only the dates that didn't have a serial number will be correct, the rest will then need to be corrected manually.

 

[Yugaa2010]

You can use this formula to modify the dates:
=DATE(RIGHT(A1,4),LEFT(A1,2),LEFT(RIGHT(A1,7),2))


edited to change semicolons to commas to make the formula work on non-Dutch Excel versions.

Thanks for your reply ?

 

[Yugaa2010]

The suggestion in post 2 will only work if the day of the date in question is on or after the 13th.

Days on or before the 12th will result in day and month being reversed, i.e. 1st March will be read as 3rd January. Because both are valid with your system settings, the right and left functions will look at the date serial instead of the actual date and give you an output of something like 12/08/3836.

The most reliable way to resolve the problem is to convert the dates in dd/mm/yyyy format to mm/dd/yyyy format using text to columns, by selecting the whole range of dates (one column at a time if multiple columns) then following the steps below on the excel ribbon.

Data tab > Text to columns > Next > Uncheck all boxes > Next > Select your correct date format from the dropdown (MDY) > Finish.

The 'Timezone' of excel is linked to your system settings, so to change it you would need to change the regional settings of your pc, doing this could create more problems than it fixes!

Hi,

Thanks for your reply.

But while I am doing text to columns, dates are not changing.

Any other solution ?

 

[jasonb75]

If text to columns is not working then it is highly likely that you have something like non breaking spaces left over from the import.

See if the solution here Get Rid of Those #$@& Spaces In Imported Data - FREE Microsoft Excel Spreadsheet Tips and Tutorials fixes it. You might need to redo text to columns afterwards for it to take effect.