Can I reference the date format in a cell in a table onto another cell on the main worksheet?

[Smidge18]

Hi all: Thanks for taking the time to look at this. Getting a bald spot on my head from scratching. I have a timesheet that is in 2 languages, English and Spanish. When I click on my dropdown language on the main worksheet, the words chane to either English or Spanish. I can't figure out, or know if it's possible, to automatically change the date format from English to Spanish in D9 on the main worksheet when I change the language in the dropdown. Is this possible? Looking forward to hearing from someone on this. Thanks


Main Worksheet

B D U
2 (dropdown)Language
8 Date
9 Sunday
=Languages!$B$3

Worksheet with table:

B C D E
1 English ID English(header) español(header)
='Template 2'!U2

2 Sunday 2 Sunday domingo
=HLOOKUP($B$1,$D$1:$E$47,C2,0)

34 0 (zero is displayed here now) 34 (blank) (blank)
=HLOOKUP($B$1,$D$1:$E$47,C34,0) (format: date March 14, 2012) (format: date:
14 de marzo de 2012)

 

[offthelip]

You can define a rule using conditional formatting to change the format depending on the language

 

[programsam]

Having a hard time understanding your formatting but maybe something like this can point you in the direction.

=IF($B$2="English",TEXT(TODAY(),"MMMM DD, YYYY"),TEXT(TODAY(),"[$-C0A]MMMM DD, YYYY"))

Looking up the TEXT Function using language code (a.k.a. "LCID") should provide assistance if this does not work.

Hi all: Thanks for taking the time to look at this. Getting a bald spot on my head from scratching. I have a timesheet that is in 2 languages, English and Spanish. When I click on my dropdown language on the main worksheet, the words chane to either English or Spanish. I can't figure out, or know if it's possible, to automatically change the date format from English to Spanish in D9 on the main worksheet when I change the language in the dropdown. Is this possible? Looking forward to hearing from someone on this. Thanks


Main Worksheet

B D U
2 (dropdown)Language
8 Date
9 Sunday
=Languages!$B$3

Worksheet with table:

B C D E
1 English ID English(header) español(header)
='Template 2'!U2

2 Sunday 2 Sunday domingo
=HLOOKUP($B$1,$D$1:$E$47,C2,0)

34 0 (zero is displayed here now) 34 (blank) (blank)
=HLOOKUP($B$1,$D$1:$E$47,C34,0) (format: date March 14, 2012) (format: date:
14 de marzo de 2012)

 

[Smidge18]

Having a hard time understanding your formatting but maybe something like this can point you in the direction.

=IF($B$2="English",TEXT(TODAY(),"MMMM DD, YYYY"),TEXT(TODAY(),"[$-C0A]MMMM DD, YYYY"))

Looking up the TEXT Function using language code (a.k.a. "LCID") should provide assistance if this does not work.

programsam, thanks for your input. I copied your 'code' into b34 of my table worksheet. cell d9 on my main worksheet showed octubre 14, 2018, so I changed the[$-COA] to [$-0409] and got October 14, 2018. Perfect...but when I go to my main worksheet and change the dropdown language to español, d9 still shows October 14, 2018...so I copied your code and added it to the end of the first one =IF($B$2="English",TEXT(TODAY(),"MMMM DD, YYYY"),TEXT(TODAY(),"[$-0409]MMMM DD, YYYY"))&IF($B$2="español",TEXT(TODAY(),"dd mmmm, YYYY"),TEXT(TODAY(),"[$-080a]dd mmmm, YYYY")) and changed the language code to [$-080a]. Now I show

October 14, 201814 octubre, 2018. It's getting closer. Any ideas? Thanks again.

<colgroup><col width="361"></colgroup><tbody>
</tbody>

 

[programsam]

You only need one if statement (so no "&" to join two different one's) because IF resolves to true or false so it looks something like this: =IF(this statement is true, DO THIS (true), OTHERWISE DO THIS (false)).

In your case, you want it look like this. =IF(this cell = "English", then display the date in English (because the statement is true), display it in spanish using the same code but adding the [brackets] code which specifies the alternate language you want displayed (because the statement is NOT true - it did not equal "English"))

You have two selections, English or Spanish so IF you set it up to do something when you select "English" then the true formula would be =TEXT(TODAY(),"DD MMMM, YYYY"). The false statement would be what you want it to display if you DON'T select English. Something like =TEXT(TODAY(),"[$-80A]MMMM DD, YYYY")

Again, this works great because you only have two options (if you had 3+ you'd have to do more) but you're getting two separate dates because you're basically executing two formulas and adjoining them together with the "&"... you should not need to do that if I understand what you're trying to achieve.

programsam, thanks for your input. I copied your 'code' into b34 of my table worksheet. cell d9 on my main worksheet showed octubre 14, 2018, so I changed the[$-COA] to [$-0409] and got October 14, 2018. Perfect...but when I go to my main worksheet and change the dropdown language to español, d9 still shows October 14, 2018...so I copied your code and added it to the end of the first one =IF($B$2="English",TEXT(TODAY(),"MMMM DD, YYYY"),TEXT(TODAY(),"[$-0409]MMMM DD, YYYY"))&IF($B$2="español",TEXT(TODAY(),"dd mmmm, YYYY"),TEXT(TODAY(),"[$-080a]dd mmmm, YYYY")) and changed the language code to [$-080a]. Now I show

October 14, 201814 octubre, 2018. It's getting closer. Any ideas? Thanks again.

<tbody>
</tbody>

 

[programsam]

Maybe try:

=IF($B$2="English",TEXT(TODAY(),"DD MMMM, YYYY"),TEXT(TODAY(),"[$-80a]DD MMMM, YYYY"))

 

[Smidge18]

Sorry for the terribly worded question and thanks very much for trying to help. Here's, hopefully, a better worded question on what I'm trying to do.

This is a worker's timesheet. I want to be able to type dates in d9:d431 and/or f9:f431 on main worksheet (Template 2) and have them change to the proper language format when I use the language dropdown cell - u2. There are 2 worksheets, the main (Template 2) and the one with a table (Languages)

Main Worksheet is named Template 2

b2 -(blank)
b8 -day of the week
b9 -Sunday - linked to Languages Worksheet table with - =Languages!$B$2. This changes to domingo when changing dropdown language to español.
b10:b15 -rest of the days of the week. This continues for 53 weeks with spaces separating each week.
b10 and the rest of the days of the week are linked to the languages worksheet - =Languages!$B$3:=Languages!$B$8. This repeats for 52 weeks
column D is Date (paid by the day). In Mexico there are workers that get paid in either category.
column F is Date (paid by the hour) this is followed by columns stating the 'from' and 'to' hours of the days worked, followed by some calculations. This is working fine.
u2 -language dropdown cell with English and español.

'Languages' Worksheet with table:

column b contents is what changes to either English or español and is linked to corresponding words on Template 2 using = sign on Template 2 worksheet for each cell with text.

b1 English -linked to language dropdown cell on other worksheet with - ='Template 2'!U2. Changes to español by using dropdown cell on other worksheet
c1 -ID
d1 -English
e1 -español

b2 -Sunday. Links to table with: =HLOOKUP($B$1,$D$1:$E$47,C2,0) - changes to domingo when changing u2 to español
on other worksheet.

c2 - 2
d2 - Sunday
e2 - domingo

b3 - Monday. Links to table with: =HLOOKUP($B$1,$D$1:$E$47,C3,0) - changes to lunes when changing u2 to español
on other worksheet.

d34 - is blank. Have formatted this cell to date - March 14, 2012
e34 - is blank. Have formatted this cell to date - 4 de marzo de 2012