Help with replacing "/" with "_" in date formula

[tmpollard]

Hello all,
Please help me with a formula to fix my date. In cell A10 I have a date 06/17/23, my macro concatenates a text cell with this date and it looks similar to this in cell A11 "Daily Report 06/17/2023".

The contents of A11 are used in the macro to save the file but it kicks it out because you can't save the file with "/" in the name. How can I enter a formula to change the "/" to "_" so the file will save.

End goal is to have a name like "Daily Report 06_17_2023" so the file will save.
Thanks

 

[Joe4]

Is the value in cell A10 actually entered as a Date or Text?
If it is entered as a valid date, you can do this to build the name in VBA:

Dim fName as String
fName = "Daily Report " & Format(Range("A10").Value,"mm_dd_yyyy")

 

[tmpollard]

This is how the date gets into cell A10, the cell is formatted as a date. Do I put your code in here somewhere?


Sub SetDate()
'
' SetDate Macro

With Range("A10")
.Value = Date
.NumberFormat = "mm/dd/yy"


End With
End Sub

 

[Joe4]

This is how the date gets into cell A10, the cell is formatted as a date. Do I put your code in here somewhere?


Sub SetDate()
'
' SetDate Macro

With Range("A10")
.Value = Date
.NumberFormat = "mm/dd/yy"


End With
End Sub

Yes, you put my code in the macro you are using to save the file. That is not the one that you posted.

Note that in your code above, it looks like you are just placing the current date in cell A10.
If all you are using A10 is to build your file name, you may not even need that code above, as you can build the file name directly without having to reference cell A10, like this:

Dim fName as String
fName = "Daily Report " & Format(Date,"mm_dd_yyyy")

If you are having trouble where to put this all in your code, please post the code you currently have to save the file.