Forum Discussion
Displaying a date using an excel vba macro
Hello. I've formatted a 4-digit numeric field 0 decimals which contains the year 2024 in cell E2. The user can change this value as needed.
I am concatenating the month and the day using the DateSerial function to cell E2 which contains the value 2024 .
The result field is a date field where I formatted it as the 1st in the list which displays the sample: *3/14/2004 .
The resulting date field is displaying the following:
4/15/2000
6/15/2000
9/15/2000
1/15/2001
It should display:
4/15/2024
6/15/2024
9/15/2024
1/15/2025
Can somebody provide me with suggestions to correct my result?
Following is my code. Thank you for your help.
Dim Currmonth As Integer
Dim Currday As Integer
Dim Curryear As Integer
Currmonth = 4
Currday = 15
Curryear = E2
Range("C10:C10").Value = DateSerial(Curryear, Currmonth, Currday)
Currmonth = 6
Range("C11:C11").Value = DateSerial(Curryear, Currmonth, Currday)
Currmonth = 9
Range("C12:C12").Value = DateSerial(Curryear, Currmonth, Currday)
Currmonth = 1
Curryear = E2 + 1
Range("C13:C13").Value = DateSerial(Curryear, Currmonth, Currday)
.
4 Replies
I'm by no means a VBA expert. Just change line where you pick-up the year number to:
Then it will work.
- Hi Riny,
Thank you for your help!
I made the change. It works!
Also, I used Nikolino's code
to change the last year entry
to Curryear = Curryear + 1.
Thank you all for your help since date
fields are so difficult to work with.
From Tommee
In your code, Curryear is assigned the value of cell E2, which is treated as a numeric value. To handle it as a year, you should use the Year function instead.
Dim Currmonth As Integer Dim Currday As Integer Dim Curryear As Integer Curryear = Year(Range("E2").Value) Currmonth = 4 Currday = 15 Range("C10:C10").Value = DateSerial(Curryear, Currmonth, Currday) Currmonth = 6 Range("C11:C11").Value = DateSerial(Curryear, Currmonth, Currday) Currmonth = 9 Range("C12:C12").Value = DateSerial(Curryear, Currmonth, Currday) Currmonth = 1 Curryear = Curryear + 1 Range("C13:C13").Value = DateSerial(Curryear, Currmonth, Currday)This code retrieves the year from cell E2 using the Year function and then uses it in the DateSerial function to create the desired dates.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Thank you NikolinoDE!
I just copied your code as described.
Unfortunately, my year is displaying
as 1905 and the last entry as 1906.
I appreciate your help. From Tommee
