Error #VALUE with TEXT(A1;"[HH]:mm") when changing regional settings

[Thierry De Bleye]

This formula works fine with French regional settings

=TEXT(A1;"[HH]:mm") (A1= 65:20)

This formula is used in a sheet for the calculation of the
overtime (working time)

but doesn't work anymore when I change the regional
setting from French to Danish

It has nothing to do with the list separator ; or ,
and not either with some add-ins

Does anyone have some idea ?

Regards
Thierry

 

[Dave Peterson]

Untested (I would have had to reboot my pc and that takes too long!):

Do the Danes use HH for hours and mm for minutes?

This formula works fine with French regional settings

=TEXT(A1;"[HH]:mm") (A1= 65:20)

This formula is used in a sheet for the calculation of the
overtime (working time)

but doesn't work anymore when I change the regional
setting from French to Danish

It has nothing to do with the list separator ; or ,
and not either with some add-ins

Does anyone have some idea ?

Regards
Thierry

 

[Thierry De Bleye]

I got the answer , the danes use [TT]:mm instead of [HH]:mm

do somebody knows how to get a list of used time format
per countries ?

Regards

Thierry

-----Original Message-----
Untested (I would have had to reboot my pc and that takes too long!):

Do the Danes use HH for hours and mm for minutes?

This formula works fine with French regional settings

=TEXT(A1;"[HH]:mm") (A1= 65:20)

This formula is used in a sheet for the calculation of the
overtime (working time)

but doesn't work anymore when I change the regional
setting from French to Danish

It has nothing to do with the list separator ; or ,
and not either with some add-ins

Does anyone have some idea ?

Regards
Thierry

--

Dave Peterson
(e-mail address removed)
.

 

[Harlan Grove]

I got the answer , the danes use [TT]:mm instead of [HH]:mm

do somebody knows how to get a list of used time format
per countries ?

...

I can't answer your question, but if you're willing to use a user-defined
function (UDF) as well as formatting specimen cells with needed numeric formats,
you could use the following.

In French, give cell X99 the custom number format "[HH]:mm". Then paste the
following UDF into a general VBA module.

Function nf(c As Range) As String
nf = c.NumberFormatLocal
End Function

Then use this UDF in your TEXT function calls, e.g., =TEXT(A1,nf(X99)). Other
that that, John Green et al's book on Excel VBA programming covers
internationalization issues.