Regional options

[maheshr68]

Greetings!!

trying dateif to calculate, but getting value error as below


3/16/2011 3/15/2011 =DATEDIF(F2,E2,"d") 1 (works fine)

16/3/2011 15/3/2011 =DATEDIF(F3,E3,"d") (#VALUE!)
16/3/2011 15/3/2011 =DATEDIF(F3,E3,"md") (#VALUE!)

Best Regards,
Mahesh

 

[Darren Bartrup]

Surely that's a regional thing as your post title says.

with your settings 3/16/2011 translates to 16th March 2011 and Excel calculates it.

But 16/3/2011 translates as 3rd of which month 2011?

Edit:
I take it you're importing data that is coming in in both formats and want a solution.
The problem there is how do you decide whether 2/1/2011 is 2nd Jan, or 1st Feb? Can you get the source file to give it in the correct format?

 

[maheshr68]

mm/dd/yyyy dates work with excel fine

dd/mm/yyyy dates have the #VALUE! error


<quote> 16/3/2011 translates as 3rd of which month 2011?

16/3/2011 translates to 16th March 2011 in regional settings

any possible method to deal with this?

Best Regards,
Mahesh

 

[Darren Bartrup]

All I can think of is the Control Panel in Windows and change your location, but then dd/mm/yyyy will stop working.

 

[maheshr68]

Greetings!

As Darren points - changing the mm/dd/yyyy to dd/mm/yyyy does not help and the value error persists..

Any valuable pointer to solve this - trying to build on the result obtained thro this to write IF conditions further.

Best Regards,
Mahesh