Problems with excel EDATE function in other countries


I have a spreadsheet that makes use of the EDATE function, which works fine in the UK.

If I send this file to a colleague in Asia or Norway for example, as soon as they recalculate, all cells turn to errors.

If they send file back, as soon as I recalculate the file, the errors clear.

Their PC's will be set up to display dates in local format and puntuation, which is what I suspect causes the error.

Any ideas how I can work around this, and create a spreadsheet that will work any where in the world?

What I need is to be able to take any date, and calculate the date that is x months less one day in the future

For example, if x = 12, then 15th March 2012 -> 14th March 2013, and 1st March 2012 -> 28th Feb 2013



Discussion is locked
Reply to: Problems with excel EDATE function in other countries
PLEASE NOTE: Do not post advertisements, offensive materials, profanity, or personal attacks. Please remember to be considerate of other members. If you are new to the CNET Forums, please read our CNET Forums FAQ. All submitted content is subject to our Terms of Use.
Reporting: Problems with excel EDATE function in other countries
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
- Collapse -
Re: formula

Two questions:
1. Is the source field a number formatted as date, or a string?
2. What is the exact formula you use?


- Collapse -

the cell is formatted as a date, so if A1 contains a date, B1 might contain =EDATE(A1,12)-1


- Collapse -
Re: Edate error
- Collapse -
Re: Update error

Its not that, because the file contains an auto running macro to load both of the toolpaks. If they change regional settings on their pc's to UK settings, and force a re-calc, the errors clear.

It appears EDATE doesn't like working other than in a UK environment.

All pc's running XP pro and office 2003


- Collapse -
Have found a work around for this problem

If date is in A1, then B1 could be Date(Year(A1),Month(A1)+12,Day(A1))-1

CNET Forums