Excel date weirdness

  interzone55 16 Apr 12
Locked
Answered

I'm just updating my holiday calendar in Excel 2007 and noticed some distinct oddness.

April 30th is a Monday, drag the cell down and Excel claims May 1st is a Sunday. Drag may right down and 31st is showing as Tuesday, next cell June 1st and we're back to Sunday.

Anyone know what's going on here?

  lotvic 16 Apr 12

As a first step I'd try re-doing the cell format for Mon April 30th and then drag down to see if it has righted itself.

Or try it out on a workbook/new sheet to test if is affecting all of Excel, and maybe do a Detect & Repair.

  interzone55 16 Apr 12

lotvic

I've already tried that, something's gone very wrong with my copy of Excel, and I have a feeling it's something to do with the last update

  Woolwell 16 Apr 12

What custom format are you using?

  compumac 16 Apr 12

Select the range for the date. Format the cells/Custom. Then type "dddd dd mmmm yyyy". Do not include comma's. Note the spaces between dddd dd mmmm yyyy. Insert the first three dates manually. Select the area for those three dates and then click on the bottom right of that area when it forms a hairline cross and then drag down, this should then give you the right sequence for the selected range.

  Woolwell 16 Apr 12

compumac - In Excel 2007 you should be able to input the first date only and then drag down and fill in the rest of the dates. This works on my version of Excel 2007.

  compumac 16 Apr 12

Woolwell Agreed - My mind was going back to older versions of Excel.

  wiz-king 16 Apr 12

Have you got any 'hidden' lines?

  interzone55 16 Apr 12

It's a clean spreadsheet

In A1 I have 01/05/2012 formatted as dd/mm/yyyy In A2 I have =day(A1) formatted as ddd

The result is Sunday when it should be Tuesday

If I drag the cells down the 31st May shows as Tuesday (it should be Thurday), then 1st June goes back to Sunday (should be Friday)

The same happens with every month from now on, Excel thinks the first of the month is a Sunday.

This is my work laptop, so I can't tamper with anything. I can't try to replicate it on my home PC as I've got Excel 2010

  Woolwell 16 Apr 12

I'm getting puzzled with A2 and then drag down. I guess that you are selecting A1 and A2 and then dragging down. If I put =day(a1) in cell A2 I also get Sun. I'll investigate this

Simpler is to format A1 as dddd dd/mm/yyyy which then drags down correctly.

  compumac 16 Apr 12

If it is a clean worksheet on your works laptop, why can you not change the configuration as per my earlier posting? It will only effect that new clean worksheet and nothing else.

Advertisement

This thread is now locked and can not be replied to.

Should I upgrade to Windows 10? 8 reasons why you should upgrade to Windows 10... and 2 why you…

We are being sold the ability to spend money we don't have. And we love it

IKinema aims to banish droopy shoulders and wonky spines in animated CG characters

How to use Apple Music in the UK: Complete guide to Apple Music's features

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message