Excel; Date Function

Question

Hello: I have a question on the date function.

Say A1 - A3 have data that show up as :

10/3

11/5

12/10

I want to add 2021 at the end of each cell so the cells reads, 10/3/2021, 11/5/2021, etc. for about 40 cells. Is there an efficient way to do this, without dealing with each individual cell.

Thanks

Answer 1

Second, can you briefly go into the logic of this.

The dates in Excel are actually numbers with the starting date being 01-Jan-1900 which is 1..

So 365 is 30-Dec-1900.

If you add a 0 via a formula to a text like say 30/6 , excel will convert to a date serial number (44742) assuming the year to be the current year (which is 2022).

since you wanted 2021, we subtracted 365 (30-Dec-1900) from the date text.

I put 30-Dec-1900 instead of 365 so that we can skip the step of converting it to date format.

you can try putting 365, convert it to date format (press ctrl + shift + #) or or select a format of your choice using Format cells

and then do the paste special > subtract

can you advise to do the very same thing but add 2022 to the date; I tried to use January 1, 1900, and that adds 2022 to the date but subtracts the date

going by the logic as explained above, for 2022

enter a 0 in some cell (since 2022 is the current year),

format it as date (press ctrl + shift + #) - it will show 00-Jan-1900 copy the cell , paste special > add on your data

Answer 2

try this

enter the date 30-dec-1900 in a cell

copy the above

select the data

paste special > subtract.

Answer 3

1. Select the column
2. Select Text to columns (On Data ribbon)
3. Select Fixed width -> Next -> Next
4. Click Date
5. Select the Date format from the DropDown (Note that you select the format that the date is already in. eg if d/m then select DMY or if the date is m/d then select MDY
6. Click Finish
7. Use Number Format to set as Date Format if year not displayed
8. Year defaults to current year (2022) rather than 2021
9. Select "Find & Select" (On Home ribbon) and then select Replace
10. Enter 2022 in the "Find what" field and 2021 in the "Replace with" field
11. Click "Replace all"

Answer 4

No problem, select the column

Right click > Select Format cells

.

You have 2 options. You can check the predefined "DATE" formats to see if the one you want exists, or you can select "Custom" and define your preferred custom format, ie M/D/YYYY or MM/DD/YYYY

Answer 5

I would try =datevalue(a1&"/2021") ddragged down and format the cells as Date.

Andreas.