Substract two dates in spanish format

G

Guest

I have a large worksheet with dates that are in Spanish format: dd/mm/aaaa.
My Excel and Operating system are in English, so when I want to substract two
dates the result is wrong as it considers: mm/dd/aaaa. I've tried to put the
format in these cells in Spanish but it still doesn't work. It is driving me
nuts and I've got thousands of dates, cannot consider changing them manually.
Any ideas?

Thxs in advance.
 
J

Jason Morin

How about converting them to mm/dd/aaaa with:

=DATE(RIGHT(A1,4),MID(A1,FIND("/",A1)+1,2),LEFT(A1,FIND
("/",A1)-1))

HTH
Jason
Atlanta, GA
 
N

Niek Otten

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
N

Niek Otten

Now that I think about it a bit more, are you sure? Aren't the dates text?
Otherwise, why never a day > 12?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
G

Guest

Thks! this worked perfectly

Jason Morin said:
How about converting them to mm/dd/aaaa with:

=DATE(RIGHT(A1,4),MID(A1,FIND("/",A1)+1,2),LEFT(A1,FIND
("/",A1)-1))

HTH
Jason
Atlanta, GA
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Substract days which format is in Spanish 3
Format dates from English to Spanish 2
Display date in spanish 3
Dates format 2
Date Format 3
Formula to figure out months between two dates 3
Sorting dates, ignoring year 3
Macro or formula for dates 1

Top