# How to format the cell date if current year come automatically at the end..



## pankajgrover (Dec 14, 2022)

HI sir,
I want to know that if i put value like in cell date just type 14.12 , and i want it picks current year automatically like in last 2022 or 22. and final date will be like 14.12.22 or 14.12.2022. Thanks


----------



## Joe4 (Dec 14, 2022)

The following code will automatically tack a period and the current year on to the end of the entry you make.
I wrote the code to work for just column C, but it is easy to adjust it to apply it to any range that you want.

In order for this code to work, the code MUST be placed in the proper Sheet module.
The easiest way to do this is to go to the worksheet you want to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window that pops up:

```
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
    
'   Only run if column C (3rd column) is updated
    If Target.Column = 3 And Target <> "" Then
        Application.EnableEvents = False
'       Tack on "." and current year to end of entry
        Target.Value = Target.Value & "." & Year(Date)
        Application.EnableEvents = True
    End If
    
End Sub
```
Now as you make entries into column C, it will automatically tack the year on to the end.


----------



## pankajgrover (Dec 16, 2022)

Joe4 said:


> The following code will automatically tack a period and the current year on to the end of the entry you make.
> I wrote the code to work for just column C, but it is easy to adjust it to apply it to any range that you want.
> 
> In order for this code to work, the code MUST be placed in the proper Sheet module.
> ...


Hi sir, First thanks for reply. I also want to know that is this possible to this without vba coding ?

And 2nd, can i use today () option if i enter anything in cell, for example in A1 if i put any data then only A2 auto populate today date, using today() , if blank then no populate... how can i do this?
Thanks


----------



## Joe4 (Dec 16, 2022)

Well, I think that to do it without VBA would probably require you to update the Regional Date settings in Windows on your computer (which would affect ALL programs, not Excel).
I have never tried that, but you may be able to do something with that.

Most of the time, to create a "DateStamp" in Excel, you would use VBA for that also.
However, you may be able to do it without VBA by making use of a Circular Reference formula.
See here for details on that: How to insert timestamps in excel sheet using formulas

The reason why you cannot just use a straight up formula like this:

```
=IF(A1="","",TODAY())
```
is because in that formula, TODAY() will ALWAYS return the current date (it will not "capture" that date at a point in time).


----------



## pankajgrover (Dec 17, 2022)

Joe4 said:


> Well, I think that to do it without VBA would probably require you to update the Regional Date settings in Windows on your computer (which would affect ALL programs, not Excel).
> I have never tried that, but you may be able to do something with that.
> 
> Most of the time, to create a "DateStamp" in Excel, you would use VBA for that also.
> ...


Yeah its true, i did not think before. if i use TODAY() function it will change my previous date also as new date comes.
So its better to use VBA code i think so. Also i want to confirm that if i use VBA code method then same excel file should be run exactly also in my mobile android using office 365 android app ? Thanks.


----------



## Dave Patton (Dec 17, 2022)

T202212a.xlsmB217-Dec-223e

What are your computer's Regional Settings for Dates?
This post was written with a computer with Regional Setting for short date dd-mm-yy.
If I enter 17-12 and press Enter, the computer enters it as the current date 17-12-2022
You can format the date to the format that you prefer.

If your computer has settings like 17.12.22, try entering 17.12 and press Enter.  Format the date to your preferred format.


----------



## jdellasala (Dec 17, 2022)

pankajgrover said:


> HI sir,
> I want to know that if i put value like in cell date just type 14.12 , and i want it picks current year automatically like in last 2022 or 22. and final date will be like 14.12.22 or 14.12.2022. Thanks


When I enter *12/14* (US Date format) into a cell, the VALUE is automatically set to 12/14/2022, however the DISPLAY format is *dd-mmm*, so displays as *14-Dec*. Changing it to the default Short Date FORMAT *mm/dd/yyyy* will DISPLAY that same date entered as 12/14 as *12/14/2022*.
Remember, the underlying VALUE in the cell is a whole number between 1 (1/1/1900) and the date - in this case *44909* which is the number of days between 1/1/1900 and 12/14/2022.
VBA has NOTHING to do with this and shouldn't.


----------



## pankajgrover (Dec 17, 2022)

Dave Patton said:


> T202212a.xlsmB217-Dec-223e
> 
> What are your computer's Regional Settings for Dates?
> This post was written with a computer with Regional Setting for short date dd-mm-yy.
> ...


I have date format dd/mm/yyyy in regional settings. But i enter in cell like this 17.12 and it's comes 17/12/1900 instead of 17.12.2022. How can i fix this 1900 . I want 2022 year.


----------



## jdellasala (Dec 17, 2022)

pankajgrover said:


> I have date format dd/mm/yyyy in regional settings. But i enter in cell like this 17.12 and it's comes 17/12/1900 instead of 17.12.2022. How can i fix this 1900 . I want 2022 year.


I'm confused. The screenshot shows 17-Dec-22 which IS 17/12/2022, not 17/12/1900. Change the format of that cell to *General*, and see what the actual value is. For 17/12/2022 it would be *44912*, for 17/12/1900 it would be *352*. ALSO, when you enter 17/12, the format of the cell can NOT be Text, or all bets are off. It must be General or some format of a Date.


----------



## pankajgrover (Dec 17, 2022)

jdellasala said:


> I'm confused. The screenshot shows 17-Dec-22 which IS 17/12/2022, not 17/12/1900. Change the format of that cell to *General*, and see what the actual value is. For 17/12/2022 it would be *44912*, for 17/12/1900 it would be *352*. ALSO, when you enter 17/12, the format of the cell can NOT be Text, or all bets are off. It must be General or some format of a Date.


I just check again. I think now problem solved now. Actually my date format was dd/mm/yyyy in regional settings and in cell also. And i was trying to put 17.12 instead of 17/12 or 17-12. when i  put value 17.12 it becomes 17/01/1900. so it means only / or - separator works. Thanks for guide me.


----------



## pankajgrover (Dec 14, 2022)

HI sir,
I want to know that if i put value like in cell date just type 14.12 , and i want it picks current year automatically like in last 2022 or 22. and final date will be like 14.12.22 or 14.12.2022. Thanks


----------



## jdellasala (Dec 17, 2022)

pankajgrover said:


> I just check again. I think now problem solved now. Actually my date format was dd/mm/yyyy in regional settings and in cell also. And i was trying to put 17.12 instead of 17/12 or 17-12. when i  put value 17.12 it becomes 17/01/1900. so it means only / or - separator works. Thanks for guide me.


Be aware that *Language *under Excel's Options has little to do with the default formats. Those are set by the OS. In Windows 11, they're under *Settings -> Time & language*, under *Regional Format*:




Although it's not apparent here, it's where you set the default Date format using the *Change formats* button:



That's showing the default Short Date format and the choices available.


----------



## pankajgrover (Dec 17, 2022)

jdellasala said:


> Be aware that *Language *under Excel's Options has little to do with the default formats. Those are set by the OS. In Windows 11, they're under *Settings -> Time & language*, under *Regional Format*:
> View attachment 81120
> Although it's not apparent here, it's where you set the default Date format using the *Change formats* button:
> View attachment 81123
> That's showing the default Short Date format and the choices available.



Yeah i was talking OS settings date format. You can see in pic short date format is set as dd/mm/yyyy. But in excel in cell 17-12 or 17/12 works and represent 2022 year but 17*.*12 not works i do not why it represent this figure 17/01/1900.. but my issue solved if i use separator / or  -


----------



## jdellasala (Dec 17, 2022)

pankajgrover said:


> Yeah i was talking OS settings date format. You can see in pic short date format is set as dd/mm/yyyy. But in excel in cell 17-12 or 17/12 works and represent 2022 year but 17*.*12 not works i do not why it represent this figure 17/01/1900.. but my issue solved if i use separator / or  -


What OS? Mac?


----------



## pankajgrover (Dec 17, 2022)

jdellasala said:


> What OS? Mac?


Windows 11


----------



## jdellasala (Dec 17, 2022)

pankajgrover said:


> Windows 11


Where did the screenshot come from? Windows *Settings -> Time & language*, under *Regional Format*? With Excel closed? The screenshot is VERY different than the Win 11 Interface.


----------



## pankajgrover (Dec 17, 2022)

jdellasala said:


> Where did the screenshot come from? Windows *Settings -> Time & language*, under *Regional Format*? With Excel closed? The screenshot is VERY different than the Win 11 Interface.


I click on control panel,  Regional settings and there i take screenshot. This is windows 11. In windows 11 you can go Regional format from either control panel or through settings interface.. yours date working with dot separator or not ? 17.12 ?


----------



## jdellasala (Dec 17, 2022)

pankajgrover said:


> I click on control panel,  Regional settings and there i take screenshot. This is windows 11. In windows 11 you can go Regional format from either control panel or through settings interface.. yours date working with dot separator or not ? 17.12 ?


I just tried it that way, and it seems to have flowed through, but to be sure use Windows 11's *Setting *app as noted above.


----------

