Language format - Date fields

hcabs99

Board Regular
Joined
May 9, 2006
Messages
247
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

Well-known Member
Joined
Mar 12, 2009
Messages
2,031
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

Board Regular
Joined
May 9, 2006
Messages
247
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

Well-known Member
Joined
Mar 12, 2009
Messages
2,031
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

Board Regular
Joined
May 9, 2006
Messages
247
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,216
Office Version
365
Platform
Windows
Hi, just another option - still a little cumbersome.

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

Forum statistics

Threads
1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top