ibelieveinmrreeves
Should've gone to Specsavers
Offline
Posts: 3857
|
|
« 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
That was definately my last game, honest
Offline
Posts: 22879
I can't bear it 🙄
|
|
« Reply #1 on: Friday, July 22, 2022, 13:17:11 » |
|
Where?
|
|
|
Logged
|
|
|
|
Nemo
Shit Bacon
Online
Posts: 22655
|
|
« Reply #2 on: Friday, July 22, 2022, 13:18:17 » |
|
https://imgur.com/a/LDqm3E1Image 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
Posts: 3857
|
|
« 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
Posts: 22655
|
|
« 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
Posts: 3857
|
|
« 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
Posts: 22655
|
|
« 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
Posts: 3857
|
|
« 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
Posts: 22655
|
|
« 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
Posts: 3857
|
|
« 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
Posts: 4004
We fucking love you Gumbo!
|
|
« 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
Posts: 452
|
|
« 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
|
|
|
|
|