Pages: [1]   Go Down
Print
Author Topic: Dates in Excel  (Read 9619 times)
ibelieveinmrreeves
Should've gone to Specsavers

Offline Offline

Posts: 3857





Ignore
« on: Friday, July 22, 2022, 13:15:42 »

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
Logged

Even men with steel hearts love to see a dog on the pitch.
4D
Or not 4D that is the question

Offline Offline

Posts: 21798


I can't bear it 🙄




Ignore
« Reply #1 on: Friday, July 22, 2022, 13:17:11 »

Where?
Logged
Nemo
Shit Bacon

Online Online

Posts: 21080





Ignore
« Reply #2 on: Friday, July 22, 2022, 13:18:17 »

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.
Logged
ibelieveinmrreeves
Should've gone to Specsavers

Offline Offline

Posts: 3857





Ignore
« Reply #3 on: Friday, July 22, 2022, 13:25:42 »

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!
Logged

Even men with steel hearts love to see a dog on the pitch.
Nemo
Shit Bacon

Online Online

Posts: 21080





Ignore
« Reply #4 on: Friday, July 22, 2022, 13:34:54 »

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)
« Last Edit: Friday, July 22, 2022, 13:38:09 by Nemo » Logged
ibelieveinmrreeves
Should've gone to Specsavers

Offline Offline

Posts: 3857





Ignore
« Reply #5 on: Monday, July 25, 2022, 18:25:41 »

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.
Logged

Even men with steel hearts love to see a dog on the pitch.
Nemo
Shit Bacon

Online Online

Posts: 21080





Ignore
« Reply #6 on: Monday, July 25, 2022, 21:08:21 »

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.
Logged
ibelieveinmrreeves
Should've gone to Specsavers

Offline Offline

Posts: 3857





Ignore
« Reply #7 on: Wednesday, July 27, 2022, 15:09:26 »

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.
« Last Edit: Wednesday, July 27, 2022, 15:13:18 by ibelieveinmrreeves » Logged

Even men with steel hearts love to see a dog on the pitch.
Nemo
Shit Bacon

Online Online

Posts: 21080





Ignore
« Reply #8 on: Wednesday, July 27, 2022, 15:13:30 »

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?
« Last Edit: Wednesday, July 27, 2022, 15:16:15 by Nemo » Logged
ibelieveinmrreeves
Should've gone to Specsavers

Offline Offline

Posts: 3857





Ignore
« Reply #9 on: Wednesday, July 27, 2022, 15:23:18 »

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.
Logged

Even men with steel hearts love to see a dog on the pitch.
Bewster

Offline Offline

Posts: 4004


We fucking love you Gumbo!




Ignore
« Reply #10 on: Thursday, July 28, 2022, 08:54:51 »

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).

 
Logged
Hitchinred

Offline Offline

Posts: 447




Ignore
« Reply #11 on: Friday, July 29, 2022, 22:30:40 »

If the date to move on is in cell A1, I would use Date((year (a1)+1) ,month (a1), day(a1))
Logged
Pages: [1]   Go Up
Print
Jump to: