Excel date weirdness

  interzone55 11:26 AM 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 12:44 PM 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 13:50 PM 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 13:56 PM 16 Apr 12

What custom format are you using?

  compumac 14:45 PM 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 14:50 PM 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 14:59 PM 16 Apr 12

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

  wiz-king 16:11 PM 16 Apr 12

Have you got any 'hidden' lines?

  interzone55 16:24 PM 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 17:37 PM 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 17:38 PM 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.

How to get Windows 10 now: how to download and install Windows 10 even if GWX.exe is missing

1995-2015: How technology has changed the world in 20 years

Nokia rolls out spherical camera for virtual reality apps

7 best iPhone web browser apps: Why Safari for iOS is still a better option than Chrome, Dolphin,…