Dates in Excel

[ibelieveinmrreeves]

I can't quite work out how to word this for a google search, so thought I'd post here. I use an Excel file as a my planner for work, and the dates are set out as below. (starting end of August 2021). I want to move them onto the dates for 2022-23, does anyone know how I can do that in this format? https://imgur.com/a/LDqm3E1

[4D]

Where?

[Nemo]

https://imgur.com/a/LDqm3E1

Image didn't render for me, but worked if copy pasted.

So assuming I understand what you want...

Add 365 to them? Dates in Excel are stored as numbers so that should work.

Can copy 365 from a cell, paste special and select add as well, so make it easier.

[ibelieveinmrreeves]

Yeah I don't often post images on here, it's a little confusing. I thought it would attach underneath as most do, but perhaps thats only in [img] tags.

Nemo - how do I 'add' 365 to them? The actual process is what I'm struggling with. The guy who made the file originally won't tell me how to do it (I've asked and he always just does it for me) but I'm sick of going back to him this time every year for what should surely be a simple job!

[Nemo]

Type 365 into a spare cell, then copy/cut it

Select the cells you want to add it to, right click and select Paste Special and you should get various options, one of which should be simply "add".

Failing that, you can add a new column next to it with the formula =A1+365 (replace A1 with the cell reference of the cell you want to "add" a year to) and then drag that down the column. Here's an example of that one in Google Sheets which should match plain Excel -https://docs.google.com/spreadsheets/d/14CzVPsKE5oVcc4YElHdvtv3_wBvgYs7nwNOjx7c1_Hs/edit?usp=sharing

The only way I could see this wouldn't work is if the date fields above are stored as text rather than dates. You may need to change the cell format if so, but it should be smart enough to handle them.


(Fwiw, attachments are a totally separate thing on this forum under the collapsed "additional options" menu. [img] tags should work for you normally, but I think imgur has some anti-embedded images stuff going on to prevent this)

[ibelieveinmrreeves]

Failing that, you can add a new column next to it with the formula =A1+365 (replace A1 with the cell reference of the cell you want to "add" a year to) and then drag that down the column. Here's an example of that one in Google Sheets which should match plain Excel -https://docs.google.com/spreadsheets/d/14CzVPsKE5oVcc4YElHdvtv3_wBvgYs7nwNOjx7c1_Hs/edit?usp=sharing

Thank you - this worked! I've had to hide the old date column as obviously deleting it fucks up the formula, but its not a big deal. It does what I need it to. Appreciated.

[Nemo]

Thank you - this worked! I've had to hide the old date column as obviously deleting it fucks up the formula, but its not a big deal. It does what I need it to. Appreciated.

Can copy your new column and then paste values into the same cell to replace the formula with the raw dates if you wish.

[ibelieveinmrreeves]

Can copy your new column and then paste values into the same cell to replace the formula with the raw dates if you wish.

Sorry if I'm being thick here, but I tried this and it just came up with #REF! in each box (presumably because I'm over writing the source of my formula).

Have just noticed too that my weeks are messed up at I only need Monday to Friday, so adding 365 gives me a bunch of unwanted Saturdays! Changing forumla there to +367.

[Nemo]

Sorry if I'm being thick here, but I tried this and it just came up with #REF! in each box (presumably because I'm over writing the source of my formula).

If it's coming up with #REF!, it's because it's still a formula and one or more of the things in the formula is now not working/not in the expected place. If you've pasted values, you'll never get #REF! because there's no formula any more. It sounds like you've done a "normal" paste instead.

The keyboard shortcut for paste values is control (command on mac) + shift + V, as opposed to control + v for a "normal" paste.

My maths brain doesn't think the 367 thing will work better than 365, it just means Wednesdays will now be Saturdays rather than Fridays, I think?

[ibelieveinmrreeves]

If it's coming up with #REF!, it's because it's still a formula and one or more of the things in the formula is now not working/not in the expected place. If you've pasted values, you'll never get #REF! because there's no formula any more. It sounds like you've done a "normal" paste instead.

The keyboard shortcut for paste values is control (command on mac) + shift + V, as opposed to control + v for a "normal" paste.

My maths brain doesn't think the 367 thing will work better than 365, it just means Wednesdays will now be Saturdays rather than Fridays, I think?

Gotcha, have learned how to paste values in Excel now! Who knew my CLAIT qualification would eventually be rendered useless.

I changed the Saturdays to +367 to make them Mondays, which worked. But having deleted the formula I'll just have to change it manually.

[Bewster]

With regards to dates in excel I find that using the Cell Ref + 1 or the End of Month Function (EOMonth) covers most bases.

For example, put 1/1/2023 in cell A1, in cell B1 put =A1+1, which will give you 2/1/2023

Or

Put 31/1/2023 in cell A1, in cell B1 put =Eomonth(A1,1), which will give you 28/02/2023

Put 1/1/2023 in cell A1, in cell B1 put =Eomonth(A1,0), which will give you 31/01/2023


Taking it on a level you can use the conditional formatting (which I hate) to "colour" the weekend columns

So in conditional formatting menu enter : =WEEKDAY(a$1,2)>5, and select the colour of the cell. Is the weekday value is greater than 5 (so Sat-6, Sunday = 7).

 

[Hitchinred]

If the date to move on is in cell A1, I would use Date((year (a1)+1) ,month (a1), day(a1))