Date changes to <*insert number* when you click cell and press enter?

**henryGKN** · 05-16-2014, 05:44 AM

I have copied and pasted data from Microsoft Access 2000 over to Microsoft Excel 2010

The data appears to be fine however when I go to edit the dates the date changes to this format e.g >47617

I am trying to set expirey dates so need the dates to be normal..

Any help appreciated!

p.s... I don't know much about excel so in simple terms please!

thanks

**aydeegee** · 05-19-2014, 02:09 PM

Hi henryGKN

Welcome to the forum.

47617 is the index number for 14 May 2030.

To change the display from numbers to date , Highlight all the numbers and then go to Number Format > date (Sorry I cannot remember how to do it in Excel 2003 - it was so long ago!)

Hope this helps

Regards
Alastair

**henryGKN** · 05-20-2014, 04:37 AM

Hi ayedeegee,

Thanks for the help! It worked....

However I am now trying to use the ISBLANK function and it won't work... I have selected the column I would like to apply the formula to, selected: conditional formatting, new rule, use formula to determine which cells to format, then entered =ISBLANK (A1)=TRUE.

It is saying this formula contains an error?

thanks for the help in advance!

**hemesh** · 05-20-2014, 04:38 AM

try only
=isblank($A1)

**henryGKN** · 05-20-2014, 04:48 AM

It says 'you may not use reference operators or array constants for conditional formatting criteria.....?

**hemesh** · 05-20-2014, 04:56 AM

where are you doing this ?
select range where you want to apply conditional formatting.

go to conditional formatting--->select new rule--->select use a formula to determine which cell to format--->
once you go through the last step there will be a box copy paste =isblank($A1)

btw what formula you are using

**henryGKN** · 05-20-2014, 05:17 AM

ok thanks, got it all sorted now!

I have another question also... I have made a database that hightlights dates when they have expired or are within 30 days of expirey, these dates also have names next to them. Is is possisble to categorize these by using the name? So for example, if I wanted to see all of Johns dates how would I do it?

**hemesh** · 05-20-2014, 05:41 AM

can you please upload a sample book with atleast 10-20 rows worth of data with the results you want and logic behind them.

**henryGKN** · 05-20-2014, 06:05 AM

Thanks for the help I have managed to do it now!

**hemesh** · 05-20-2014, 06:11 AM

You are welcome and thanks for the feedback

Date changes to <insert number when you click cell and press enter?

LinkBack

Thread Tools

Rate This Thread

Display