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
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

Excel date weirdness


interzone55
Resolved

Likes # 0

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?

Like this post
lotvic

Likes # 0

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.

Like this post
interzone55

Likes # 0

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

Like this post
Woolwell

Likes # 0

What custom format are you using?

Like this post
compumac

Likes # 0

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.

Like this post
Woolwell

Likes # 0

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.

Like this post
compumac

Likes # 0

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

Like this post
wiz-king

Likes # 0

Have you got any 'hidden' lines?

Like this post
interzone55

Likes # 0

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

Like this post
Woolwell

Likes # 0

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.

Like this post
compumac

Likes # 0

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.

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

Microsoft smartwatch release date, price and specs rumours: Launching within a few weeks

IDG UK Sites

Why you shouldn't buy the iPad mini 3: No wonder Apple gave it 10 seconds of stage time

IDG UK Sites

Halloween Photoshop tutorials: 13 masterclasses for horrifying art, designs and type

IDG UK Sites

Should I upgrade from Mavericks to OS X 10.10 Yosemite? What you need to know before updating to...