Thetownend.com

80% => Computer & Technology => Topic started by: ibelieveinmrreeves on Friday, July 22, 2022, 13:15:42



Title: Dates in Excel
Post by: ibelieveinmrreeves 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


Title: Re: Dates in Excel
Post by: 4D on Friday, July 22, 2022, 13:17:11
Where?


Title: Re: Dates in Excel
Post by: Nemo on Friday, July 22, 2022, 13:18:17
https://imgur.com/a/LDqm3E1 (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.


Title: Re: Dates in Excel
Post by: ibelieveinmrreeves 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!


Title: Re: Dates in Excel
Post by: Nemo 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)


Title: Re: Dates in Excel
Post by: ibelieveinmrreeves 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.


Title: Re: Dates in Excel
Post by: Nemo 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.


Title: Re: Dates in Excel
Post by: ibelieveinmrreeves 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.


Title: Re: Dates in Excel
Post by: Nemo 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?


Title: Re: Dates in Excel
Post by: ibelieveinmrreeves 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.


Title: Re: Dates in Excel
Post by: Bewster 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).

 


Title: Re: Dates in Excel
Post by: Hitchinred 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))