Hi, thanks for reading!
Using Excel 2007 and cant share file right now but it's easy to explain:
Basically, I have a formula calculating the difference between 2 cells which are custom formatted to display seconds in this way:
hh:mm:ss.000 and calculations are done with these numbers which should display in normal decimal style. When comma decimal dividers Excel versions are used when working in the file, my last formula output gives 'VALUE' error.
Here is the breakdown:
So A1 is the start time, custom formatted as hh:mm:ss.000.
A2 is finish time, also formatted as hh:mm:ss.000.
A3 is the difference A1-A2, custom formatted as 's.00'.
In A4 a number is entered (in 'General' formatting') which displays the amount of events needed to happen in the time displayed in A3:
A4 = # of events.
A5 shows the amount of events per second, but in text format so = A4/TEXT(A4, "s.000") and custom formatted to '0.00 "eps"'
So, in one example
A1 = 00:00:10.500 (10.5 seconds showing as hh:mm:ss.000)
A2 = 00:00:05.500 (5.5 seconds displaying as hh:mm:ss.000)
A3 = 5.00 (5 seconds displaying as formatted as s.00)
A4 = 5
A5 = 1 eps
However, English locales use the period point dot as the divider for the decimal place normally but several other countries use a comma.
So now, several people who work with in the same file with commas as decimal points enter in data in A4.
When these countries save Excel the formula messes up displaying 'Value', but the divider that appears in the saved file is still a dot due to custom formatting i guess.
Anyone any suggestions? Thanks!!
Using Excel 2007 and cant share file right now but it's easy to explain:
Basically, I have a formula calculating the difference between 2 cells which are custom formatted to display seconds in this way:
hh:mm:ss.000 and calculations are done with these numbers which should display in normal decimal style. When comma decimal dividers Excel versions are used when working in the file, my last formula output gives 'VALUE' error.
Here is the breakdown:
So A1 is the start time, custom formatted as hh:mm:ss.000.
A2 is finish time, also formatted as hh:mm:ss.000.
A3 is the difference A1-A2, custom formatted as 's.00'.
In A4 a number is entered (in 'General' formatting') which displays the amount of events needed to happen in the time displayed in A3:
A4 = # of events.
A5 shows the amount of events per second, but in text format so = A4/TEXT(A4, "s.000") and custom formatted to '0.00 "eps"'
So, in one example
A1 = 00:00:10.500 (10.5 seconds showing as hh:mm:ss.000)
A2 = 00:00:05.500 (5.5 seconds displaying as hh:mm:ss.000)
A3 = 5.00 (5 seconds displaying as formatted as s.00)
A4 = 5
A5 = 1 eps
However, English locales use the period point dot as the divider for the decimal place normally but several other countries use a comma.
So now, several people who work with in the same file with commas as decimal points enter in data in A4.
When these countries save Excel the formula messes up displaying 'Value', but the divider that appears in the saved file is still a dot due to custom formatting i guess.
Anyone any suggestions? Thanks!!
