Problem creating cell output from Date using VBA script in Excel

Question

I'm trying to use VBA to insert a new row and then create a reference using the date in the format yyyymmdd followed by a hyphen and then 1.

So for example, today I would like the output to read 20251117-1. I have used the code below, but this causes two problems.

The user needs to have cell A7 selected otherwise the -1 outputs in the selected cell
When A7 is selected adding the "-1" changes the way that the date displays, so for today it changes the output to 17/11/2025-1

Sub Reference()
Rows(7).Insert
Range("A7") = Date
Range("A7").NumberFormat = "yyyymmdd"
ActiveCell.Value = ActiveCell.Value & "-1"
End Sub

Frank Ball · Accepted Answer · 2025-11-17 21:43:51Z

0

This all depends on how you want to use the value in the cell you're formatting. If you want it be an actual date, but display with the "-1", you can use a custom number format. If you just want it to look like a date with the "-1" and just be text, Random3c0d3's solution works. When I look at your question, my first thought is "Is there going to be a "-2", "-3", etc. as well, so I jump to a solution that will allow the flexibility to handle any extension to the date. Using a simple routine like this allows you to pass in any range and any extension after a "-" and have the date formatted with the extension (display only), but still be a date.

Sub ShowDateSub(rng As Range, s As String)
    rng.NumberFormat = "yyyymmdd-""" & s & """"
End Sub

... or you can drop the parameters altogether and use it like this:

Range("A7").NumberFormat = "yyyymmdd""-1"""

edited 2 days ago

answered 2 days ago

Frank Ball

1,1778 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Darren Bartrup-Cook 9 hours ago

No idea why @Random3c0d3 answer was deleted. Looks like it would've worked.

Collectives™ on Stack Overflow

Problem creating cell output from Date using VBA script in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related