Text-function and Date/Time settings

[mucah!t]

Hello all,

I got a sheet that is being used by several users in with different date and time settings.

The problem with the formula I use in C2 is that it works for the english time settings but isn't for the Dutch.

Is there another way to render the cells A and B like as in C1 but isn't affected by time settings?

Excel Workbook
	A	B	C
1	6:00	14:00	06:00 - 14:00
2	6:00	14:00	HH:01 - HH:01
Sheet1

Cell Formulas
Range	Formula
C1	=TEXT(A1,"UU:MM") &" - "&TEXT(B1,"UU:MM")
C2	=TEXT(A2,"HH:MM") &" - "&TEXT(B2,"HH:MM")

 

[T. Valko]

Hello all,

I got a sheet that is being used by several users in with different date and time settings.

The problem with the formula I use in C2 is that it works for the english time settings but isn't for the Dutch.

Is there another way to render the cells A and B like as in C1 but isn't affected by time settings?

Excel Workbook
	A	B	C
1	6:00	14:00	06:00 - 14:00
2	6:00	14:00	HH:01 - HH:01
Sheet1

Cell Formulas
Range	Formula
C1	=TEXT(A1,"UU:MM") &" - "&TEXT(B1,"UU:MM")
C2	=TEXT(A2,"HH:MM") &" - "&TEXT(B2,"HH:MM")

How about this...

=HOUR(A1)&":"&TEXT(MINUTE(A1),"00")&" - "&HOUR(B1)&":"&TEXT(MINUTE(B1),"00")

 

[mucah!t]

This one seems to work, thank you #NAME!

 

[T. Valko]

This one seems to work, thank you #NAME!

You're welcome. Thanks for the feedback!