Language format - Date fields

[hcabs99]

Hi

I have a sheet which needs to be used in multiple countries. On this sheet there is a date field and a time field.

I need to combine these into 1 cell with the following format

yyyymmddhhmmss

So i was doing a very simple formula of =IF(E2>0;TEXT(G2;"YYYYMMDD")&TEXT(H2;"HHMMSS");"")

However, this sheet is being used by multiple countries, so this formatting doesnt work. i end up with YYYY1020HH0000 (when a polish user uses it)

I have changed the format of the source date cell to read [$-409] which has worked ok, but not this formula

Any ideas how i can get excel to force the English version here or indeed a better formula?

Cheers

 

[baitmaster]

Your approach will create a text string, is this what you want?

Have you tried simply adding a custom cell format (ctrl + 1) of "yyyymmddhhmmss"?

Or a formula =Year(xxx) & month(xxx) & day(xxx) etc., including multipliers if needed?

 

[hcabs99]

Your approach will create a text string, is this what you want?

Have you tried simply adding a custom cell format (ctrl + 1) of "yyyymmddhhmmss"?

Or a formula =Year(xxx) & month(xxx) & day(xxx) etc., including multipliers if needed?

Yes a text format is exactly what i need , this has been working fine for me until i passed it to a user in Poland which is when i got this problem,.

The format of the destination cell is set as [$-en-US]yyyymmddhhmmss

Also tried to force the format in the formula as well
=IF(E2>0;TEXT(G2;"[$-809]YYYYMMDD")&TEXT(H2;"[$-809]HHMMSS");"")

But i always end up with YYYY1020HH0000 as a result

Any ideas?

 

[baitmaster]

If you want a text string returned then this should work

=YEAR(A1)&REPT(0,2-LEN(MONTH(A1)))&MONTH(A1)&REPT(0,2-LEN(DAY(A1)))&DAY(A1)&REPT(0,2-LEN(HOUR(A1)))&HOUR(A1)&REPT(0,2-LEN(MINUTE(A1)))&MINUTE(A1)&REPT(0,2-LEN(SECOND(A1)))&SECOND(A1)

It's ugly, but it works...

 

[hcabs99]

If you want a text string returned then this should work

=YEAR(A1)&REPT(0,2-LEN(MONTH(A1)))&MONTH(A1)&REPT(0,2-LEN(DAY(A1)))&DAY(A1)&REPT(0,2-LEN(HOUR(A1)))&HOUR(A1)&REPT(0,2-LEN(MINUTE(A1)))&MINUTE(A1)&REPT(0,2-LEN(SECOND(A1)))&SECOND(A1)

It's ugly, but it works...

Cracking , works just fine. many thanks for this. !

 

[FormR]

Hi, just another option - still a little cumbersome.

=YEAR(G2)&TEXT(MONTH(G2),"00")&TEXT(DAY(G2),"00")&TEXT(HOUR(H2),"00")&TEXT(MINUTE(H2),"00")&TEXT(SECOND(H2),"00")