Excel is converting scores into different dates - how can I stop

Resolved · Medium Priority · Version 2016

Wed 20 Mar 2019, 13:27 Delegate neha said...

neha has attended:
Excel Intermediate course

Excel is converting scores into different dates - how can I stop

I have an attachment to send through, which shows scores in column H. Some scores start with the word "Right eye" or "Left eye" therefore the scores that follow, are accurate.

Those scores that do not have the words Right or Left eye are causing excel to convert them into dates as shown below.

Where the score is documented as;

6/12 - excel is converting it to 06-Dec
6/18 - excel is converting it to June 18
6/24 - excel is converting it to June 24

Please see e-mailed attachment. Is there a way to tell excel these are not dates.

Wed 20 Mar 2019, 13:55 Trainer Claire said...

RE: excel is converting scores into different dates - how can I

Hi Neha,

You're right. Excel is viewing the numbers - 6/18 - as a date and is applying date formatting.

The formatting it has applied is: mmm-yy so you get Jun/18.

You can change the date format so you keep the numbers which were entered.
As you've done Excel Intermediate you should have covered this, it certainly should be in the manual if you can't remember all the steps.

Click in one of the problem cells, right click and choose format cells.
Select Custom from the panel on the left. From the panel on the right, click in the field under Type: and change the format to mm/yy. You can see this change in the Sample field at the top of the right hand panel.

If you apply this new format to the problem cells they should show as they were entered.

As you said the best solution is for the person who entered the data to include the text showing which eye is referred to.

Please let me know if you need more information.

Claire

Wed 27 Mar 2019: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Ctrl+d's double life

Suppose I have a formula in B1 that I wish to copy into B2:B10. I can select B1:B10 then press Ctrl+d to copy the formula down the selected range. Users generally ignore this shortcut in favour of double-clicking on the fill handle to copy down, but Ctrl+d is useful sometimes particularly when there is no data in surrounding columns to guide to how far the double-click method should copy formulae.

Ctrl+d has another use though. When I use the drawing toolbar to draw objects such as Text Boxes, Rectangles and Ovals onto a worksheet, Ctrl+d makes an instant duplicate of selected shapes. For example, I need five Text Boxes the same size. I draw one Text box and adjust it to the size I want, select it, then press Ctrl+d four times to get four identical copies.

View all Excel hints and tips