Different date formats

[kasbac]

Hi there

I have the follwing formula working out how many days within a particular month a machine will occupied based on a starting and end date that i get from a system report.

=MAX(0;MIN(VALUE($Z2);DH$1+31-DAY(DH$1+31))-MAX(VALUE($Y2);DH$1)+1)

When I pull the report the date's (in coulmn Y and Z) comes out in a format like this: 30-07-2010 and the formula works fine.

I now intend to hand this formula over to a colleauge in an other region than where i am. When she pull out the same report the date format is shown as such: 30.07.2010. It appears as if the formuals no longer can handle this format? Does anyone have any idea how I can work around this problem?

 

[ashleywanless]

Excel doesn't recognise dd.mm.yyyy as a date. If this is how the System generates the report I would record a macro to throw at it. The macro just needs to be a find . And replace with / you should be able to use the recorder to get the code you need