Excel Problem

Misticles

I am using an IF statement...
It looks up a date and if 2 dates match the cell is blank... if they dont match, the cell shows the two dates so the user can see why they dont match.

My problem is if they dont match its not showing the dates in date format.
Its coming up as a number ie 40856 which is 9/11/2011.

Its happening for every field where I have a date.

I have tried formatting the date columns as date but it's not working.

Any ideas?

Duke Leonal Felmet

Misticles wrote: »

I am using an IF statement...
It looks up a date and if 2 dates match the cell is blank... if they dont match, the cell shows the two dates so the user can see why they dont match.

My problem is if they dont match its not showing the dates in date format.
Its coming up as a number ie 40856 which is 9/11/2011.

Its happening for every field where I have a date.

I have tried formatting the date columns as date but it's not working.

Any ideas?

Highlight 40856 cell. Hit ctrl+1. Pick 'number' and 'custom', in 'type' input dd/mm/yyyy.

Misticles

Duke Leonal Felmet wrote: »

Highlight 40856 cell. Hit ctrl+1. Pick 'number' and 'custom', in 'type' input dd/mm/yyyy.

Tried that - it didnt change anything

Duke Leonal Felmet

Misticles wrote: »

Duke Leonal Felmet wrote: »

Highlight 40856 cell. Hit ctrl+1. Pick 'number' and 'custom', in 'type' input dd/mm/yyyy.

Tried that - it didnt change anything

Are the original cells in date format?

Are you calling data from a different worksheet? Try move the data being read to the same worksheet.

Post up your IF statement.

ct5amr2ig1nfhp

Try using the TEXT function
e.g. TEXT('10/11/2011','dd/MMM/yyyy')

eg.
A1 - Date1
B1 - Date2
C1 - Function

=IF(A1=B1,"",TEXT(A1,"dd/mmm/yyyy") & " | " &TEXT(B1,"dd/mmm/yyyy") )

Not sure how you have your sheet or how you want the two dates showing, but the above might help.

Duke Leonal Felmet

If it's not personal information, you can attach the sheet here.

Taltos

Um - what do you mean the cell shows both dates?
Maybe you might be better off just putting in a constant that you can filter on?
eg "Delta" or something like that...

Your formatting issue might be down to however you are showing both dates

Duke Leonal Felmet

How about this?

Misticles

the dates are being compared and they are on 2 different sheets.
my IF is like this: =if([Book2]Sheet1!$A$1=A2,"",[Book2]Sheet1!$A$1&" ! "&A2)

so if they dont match for example...
book 2 sheet 1, A1 =01/01/11
other book is 05/01/11

the if shows it 01/01/11 ! 05/01/11

but shows 4085 ! 4055 instead .

hope that makes sense

Duke Leonal Felmet

Misticles wrote: »

the dates are being compared and they are on 2 different sheets.
my IF is like this: =if([Book2]Sheet1!$A$1=A2,"",[Book2]Sheet1!$A$1&" ! "&A2)

so if they dont match for example...
book 2 sheet 1, A1 =01/01/11
other book is 05/01/11

the if shows it 01/01/11 ! 05/01/11

but shows 4085 ! 4055 instead .

hope that makes sense

Off the top of my head, so it may not work.

Create duplicates of both date columns, but =String(a1) versions of the dates. Then point your IF statements at those columns.

Misticles

Duke Leonal Felmet wrote: »

Off the top of my head, so it may not work.

Create duplicates of both date columns, but =String(a1) versions of the dates. Then point your IF statements at those columns.

A macro opens the files that they are in, copies that file into another spreadhseet. I don't know how feasible that way would be tbh.

Thanks though

Duke Leonal Felmet

Misticles wrote: »

Duke Leonal Felmet wrote: »

Off the top of my head, so it may not work.

Create duplicates of both date columns, but =String(a1) versions of the dates. Then point your IF statements at those columns.

A macro opens the files that they are in, copies that file into another spreadhseet. I don't know how feasible that way would be tbh.

Thanks though

Ok, I solved it. Lets say a1 produces 41450, to convert it to a date, you must do this.

day(a1)&"/"&month(a1)&"/"&year(a1)

Try it out to see what I mean. I presume you can do the rest.

Misticles

Duke Leonal Felmet wrote: »

Ok, I solved it. Lets say a1 produces 41450, to convert it to a date, you must do this.

day(a1)&"/"&month(a1)&"/"&year(a1)

Try it out to see what I mean. I presume you can do the rest.

Do I put that in the formula bar of custom format it?

Taltos

formula

though not sure it will work - but give it a try - think you might have to do this for each of the fields.

eg

= day(a1)&"/"&month(a1)&"/"&year(a1)

=if([Book2]Sheet1!$A$1=A2,"",[Book2]Sheet1!$A$1&" ! "&A2)
becomes
=if([Book2]Sheet1!$A$1=A2,"",[Book2]Sheet1!day($a$1)&"/"&month($a$1)&"/"&year($a$1)&" ! "&day(a2)&"/"&month(a2)&"/"&year(a2))

Please note the symbol $ - locks the field reference - so if you drag this formula down it will always read $A$1 even if you are on row 20...
if you want the formula to roll change it to
=if([Book2]Sheet1!$A1=A2,"",[Book2]Sheet1!day($a1)&"/"&month($a1)&"/"&year($a1)&" ! "&day(a2)&"/"&month(a2)&"/"&year(a2))

Duke Leonal Felmet

Misticles wrote: »

Duke Leonal Felmet wrote: »

Ok, I solved it. Lets say a1 produces 41450, to convert it to a date, you must do this.

day(a1)&"/"&month(a1)&"/"&year(a1)

Try it out to see what I mean. I presume you can do the rest.

Do I put that in the formula bar of custom format it?

Formula bar. Let's call that formula I provided X for a1 and Y for b1. The if statement you need is:

IF(a1=b1,"",(X) & " ! " & (Y))

That will do what you want.

ct5amr2ig1nfhp

=if([Book2]Sheet1!$A$1=A2,"",TEXT([Book2]Sheet1!$A$1,"dd/mmm/yyyy")&" ! "&TEXT(A2,"dd/mmm/yyyy"))

dd/mmm/yyyy is the format I always use, but change it to whatever format you want.