Long dates: days in between?

Dear all,

I receive via e-mail Starting dates and End dates in all kind of forms:

- Friday, 12 September 2014
- Friday, September 12 2014
etc.

f.e. Friday, 12 September 2014 until Friday, 19 September 2014.

I copy these dates to excel, where I have a simple formula to calculate the number of days between the end date and the starting date.

I have 2 problems here:
- first: my excel only calculates the days between if I would enter a short date first: f.e. 12/09/2014. Fact is that I have only long dates as input.
- second: due to the different forms of the long date I would have to change my regional settings every time.

Does anyone see a possibility here in working with the long dates that are provided in different formats?

Thanks in advance!

Please find attached a working sheet:

I can't calculate the days in between in cel D3 and D6. Only in D9 it works out, because I've inputted there myself the short dates.

Hi,

Please see the attached file, I have used the following formula to convert the dates into standard format and then calculate the number of days.

In C3:

=DATE(RIGHT(B3,4),MONTH(DATEVALUE(MID(B3,FIND(" ",B3,FIND(" ",B3,1)+1)+1,3)&" 1")),MID(B3,FIND(",",B3,1)+2,2))


In C5:

=DATE(RIGHT(B6,4),MONTH(DATEVALUE(MID(B6,FIND(",",B6,1)+2,3)& " 1")),MID(B6,FIND(",",B6,FIND(",",B6,1)+1)-2,2))

This formula does the trick!
Thanks :-)

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

Also, you can directly thank those who have helped you by clicking on the small * (star) icon located in the lower left corner of a post that you have found to be helpful.