Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel not using regional settings | Excel Discussion (Misc queries) | |||
Regional settings | Excel Worksheet Functions | |||
Regional Settings with Date | Excel Discussion (Misc queries) | |||
regional settings | Excel Worksheet Functions | |||
Regional Settings | Excel Discussion (Misc queries) |