Custom data formatting

[albertoro]

Hi everyone, i need help with my spreadsheet.

In Column J2 I need to have the same date format as column I2 but for some reason id does not work.

If i right click on J2 go to "format cell" -"Custom" and select "yyymmdd" the date format showed in the "Sample" field do not change. I tried with everysingle custom type and do not change.

If I do the same process with column I2 i get the result of what i want.

could you please anyone advise what i am missing? test_W.xlsx

[Fotis1991]

Hi

Use TEXT to Columns in column H ,of BNP sheet to convert the dates there to the format that you like and then J2 will works fine.

[pareshj]

Hi,

Check the formula in cell I2 : =IF(OR(B2="RVP",B2="DVP"),BNP!G2,"ERROR")

and in J2 : =IF(OR(B2="RVP",B2="DVP"),BNP!H2,"ERROR")

If you change, formula in J2 to BNP!G2 instead of BNP!H2
You will get the result as desired.

Regards,
Paresh J

[albertoro]

Hi Fotis, could you please be more specifc? there are different options when i select Text to column - delimited

thx

[albertoro]

Paresh, thank you but I want to read BNP!H2 as the value is different.

the formula is correct (Reads the correct value) but for some reason the formatting is not.

[Fotis1991]

jUST FOLLOW THE LINK THAT I PROVIDED IN MY PREVIOUS POST

[albertoro]

Yes sorry, I didnt see that.

thx

[pareshj]

Hi albertoro,

Please check the date format in send Sheet for I column its yyyymmdd, so according to which your yr = 2014, mm = 12, dd = 06

If you check date format in BNP sheet for G column its dd-mm-yyyy, so according to that your dd = 06, mm = 12, yyyy = 2014.

But you have same format for H column in BNP sheet, so your dd = 06, mm = 17 and yyyy = 2014 and hence you dont get what you desired in send sheet since your date is wrong in H column of BNP sheet, Try entering 17-06-2014 in H column and check. Hope this will resolve your problem.


Regards,
Paresh J

[albertoro]

Fotis, followed the procedure in the link but nothing happen.

[pareshj]

Hi,

Check with my above solution in post #8. Hope it will help.

[albertoro]

Pareshj, have you tried to select BNP H2 right click on it and Format cell. go to custom. if you select any custom setting do you see the Sample changing? I dont, it always stay the original.

[pareshj]

Hi,

The reason H2 date format is not changing since you have wrongly entered the date, according to format in G2 06 is dd and 12 is mm but in H2, 06 is dd and 17 is mm and hence date format is not getting applied on that cell.

Checked the attached wrkbook :

test_W.xlsx

[Fotis1991]

This is what i have.

[albertoro]

Hi,

The reason H2 date format is not changing since you have wrongly entered the date, according to format in G2 06 is dd and 12 is mm but in H2, 06 is dd and 17 is mm and hence date format is not getting applied on that cell.

Checked the attached wrkbook :

Attachment 325219

Pareshj thank you for the time. i will review as soon as i can. not home now.

[newdoverman]

The formula in send!J2 is referring to BNP!H2. This value is TEXT. Click on BNPW!H2, then click on the Data tab, Text to columns, Next, Next, Date MDY, Finish. The date will be changed to a real date. Now, the date in send!J2 will format as yyymmdd as you want.