Regional settings problem with TEXT(value,fmt) function
 

Hi experts,
I have a problem using TEXT(value,format) function because of regional
settings.

Actually I need to get a Time only value from DateTime value.
So I use the following formula:
=Text(B2,"h:mm")

But I found that it doesn't work on the machines with f.ex. Danish regional
settings, because it must be "t:mm" for Danes.

How can I get rid of Date in DateTime cell?
Any help?

Thanks,
Yakimo

Regional settings problem with TEXT(value,fmt) function
 

you can format the cell to display time only and not use the text function.

=B2

if you want to strip off the date
=B2-trunc(B2)

and format the cell to display time. Date and time are stored as the number
of days from a reference date. So full days (the date) are represented by a
whole number and time by a fraction of a day.

123.5 would be noon, 123 days after the base date.

the above formula just gets the fractional portion.

--
Regards,
Tom Ogilvy



"Yakimoto" wrote in message
...
Hi experts,
I have a problem using TEXT(value,format) function because of regional
settings.

Actually I need to get a Time only value from DateTime value.
So I use the following formula:
=Text(B2,"h:mm")

But I found that it doesn't work on the machines with f.ex. Danish
regional
settings, because it must be "t:mm" for Danes.

How can I get rid of Date in DateTime cell?
Any help?

Thanks,
Yakimo

Regional settings problem with TEXT(value,fmt) function
 

Yes Tom,
but I prefer the value to be text (string) value, instead of a fraction,
otherwise the formula
{=SUMPRODUCT((Time=C$1)*(STN=$A2)*Missed)}
doesn't return expected, because if my if Time (range) is in DateTime as
number and C$1 is the same, the fraction doesn't match.
One simple example:

MyValue MyValue-TRUNC(MyValue) Exact value Equal
1/1/2004 0:12 1/0/1900 0:12 1/0/1900 0:12 FALSE


The last column compares "MyValue-TRUNC(MyValue)" with "Exact value" and it
gives false, i.e. they are not the same.
If I subtract them, I get
1/0/1900 0:00

which in numeric format is 1.94025524813402E-12
So internally they are not the same, even though if they have DateTime
format they look as equal.

In order to avoid these confusions with a date/times, I decided to have TEXT
time value in both cases and my formula
{=SUMPRODUCT((Time=C$1)*(STN=$A2)*Missed)}
works perfectly, if my Time range contains TimeString. But unfortunately
TIME() function =TEXT(B9,"h:mm") returns "h:01" instead of correct
TimeString
I really don't know what to do. If I remember well, there is some API call
to get all these format strings, but it seems too deep and complicated to do
that.

What do you think? Any other suggestions?

Any help is appreciated

"Tom Ogilvy" wrote in message
...
you can format the cell to display time only and not use the text
function.

=B2

if you want to strip off the date
=B2-trunc(B2)

and format the cell to display time. Date and time are stored as the
number
of days from a reference date. So full days (the date) are represented by
a
whole number and time by a fraction of a day.

123.5 would be noon, 123 days after the base date.

the above formula just gets the fractional portion.

--
Regards,
Tom Ogilvy



"Yakimoto" wrote in message
...
Hi experts,
I have a problem using TEXT(value,format) function because of regional
settings.

Actually I need to get a Time only value from DateTime value.
So I use the following formula:
=Text(B2,"h:mm")

But I found that it doesn't work on the machines with f.ex. Danish
regional
settings, because it must be "t:mm" for Danes.

How can I get rid of Date in DateTime cell?
Any help?

Thanks,
Yakimo

Regional settings problem with TEXT(value,fmt) function
 

Hi again,

I found the right solution for the function =TEXT(value,"h:mm") indepedent
of the Regional settings.
And the solution is to compose Fmt time string like that
With Application
sTimeFmtString = .International(xlHourCode) &
..International(xlTimeSeparator) & _
.International(xlMinuteCode) & .International(xlMinuteCode)
End With

..Range("F2").FormulaR1C1 = "=TEXT(RC[-4]," & """" & sTimeFmtString & """" &
")"

In this way it works doesn't matter on the Regional settings

I hope that can help someone in the future

Regards,
Yakimo

"Tom Ogilvy" wrote in message
...
you can format the cell to display time only and not use the text
function.

=B2

if you want to strip off the date
=B2-trunc(B2)

and format the cell to display time. Date and time are stored as the
number
of days from a reference date. So full days (the date) are represented by
a
whole number and time by a fraction of a day.

123.5 would be noon, 123 days after the base date.

the above formula just gets the fractional portion.

--
Regards,
Tom Ogilvy



"Yakimoto" wrote in message
...
Hi experts,
I have a problem using TEXT(value,format) function because of regional
settings.

Actually I need to get a Time only value from DateTime value.
So I use the following formula:
=Text(B2,"h:mm")

But I found that it doesn't work on the machines with f.ex. Danish
regional
settings, because it must be "t:mm" for Danes.

How can I get rid of Date in DateTime cell?
Any help?

Thanks,
Yakimo

Regional settings problem with TEXT(value,fmt) function
 

Hi,

Another solution (which doesn't involve VBA) is to use the following
formula:

=HOUR(B2) & ":" & TEXT(MINUTE(B2),"00")

--

Mikko Noromaa )
- SQL in Excel, check out ExcelSQL! - see http://www.excelsql.com -

"Yakimo" wrote in message
...
Hi again,

I found the right solution for the function =TEXT(value,"h:mm") indepedent
of the Regional settings.
And the solution is to compose Fmt time string like that
With Application
sTimeFmtString = .International(xlHourCode) &
.International(xlTimeSeparator) & _
.International(xlMinuteCode) & .International(xlMinuteCode)
End With

.Range("F2").FormulaR1C1 = "=TEXT(RC[-4]," & """" & sTimeFmtString & """"
&
")"

In this way it works doesn't matter on the Regional settings

I hope that can help someone in the future

Regards,
Yakimo