Format cell

[bobkap]

I have a variable in cell A50 in my sheet called Codes. The name of the variable is Endperdate. I want it to output in the date format as shown below, but it always just gives the Excel number for the date. No doubt I have something wrong with my code. Any help would be greatly appreciated.

Worksheets("Codes").Range("<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">A50").NumberFormat = "m/d/yyyy"
PDFFile = DestFolder & Application.PathSeparator & ws.Name _
& "_" & Endperdate & ".pdf"

 

[Joe4]

Try this:

[COLOR=#333333][FONT=Arial]PDFFile = DestFolder & Application.PathSeparator & ws.Name _[/FONT][/COLOR]
[COLOR=#333333][FONT=Arial]& "_" & [/FONT][/COLOR][COLOR=#ff0000][FONT=Arial]FORMAT(Endperdate,"m/d/yyyy")[/FONT][/COLOR][COLOR=#333333][FONT=Arial] & ".pdf"[/FONT][/COLOR]

 

[DanteAmor]

Try this:

[COLOR=#333333][FONT=Arial]PDFFile = DestFolder & Application.PathSeparator & ws.Name _[/FONT][/COLOR]
[COLOR=#333333][FONT=Arial]& "_" & [/FONT][/COLOR][COLOR=#ff0000][FONT=Arial]FORMAT(Endperdate,"m[/FONT][/COLOR][COLOR=#0000ff][FONT=Arial][B]-[/B][/FONT][/COLOR][COLOR=#ff0000][FONT=Arial]d[/FONT][/COLOR][B][COLOR=#0000ff][FONT=Arial]-[/FONT][/COLOR][/B][COLOR=#ff0000][FONT=Arial]yyyy")[/FONT][/COLOR][COLOR=#333333][FONT=Arial] & ".pdf"[/FONT][/COLOR]

But if you try to save the file with the slash (/) it will send a filename error, you should use hyphen (-).

 

[bobkap]

THANKS. Makes perfect sense. I wasn't sure if you could do that with a variable.

However, now I get an error when I run that macro with that formatting code. I get this "Run-time error '1004': Document not saved. The document may be open, or an error may have been encountered when saving."

Might you be able to help me with that please?

 

[bobkap]

Sorry. I just noticed your sentence about using hyphens. I changed to that. NOW it doesn't bomb out, but it doesn't change the format to date either. UGH.

Thanks again for the help.

 

[DanteAmor]

Sorry. I just noticed your sentence about using hyphens. I changed to that. NOW it doesn't bomb out, but it doesn't change the format to date either. UGH.

Thanks again for the help.

In A50 do you have a date or a text?

I don't understand the Endperdate variable, how do you have it declared.


If in A50 you have a date, you can do it directly like this:

PDFFile = DestFolder & Application.PathSeparator & ws.Name _
& "_" & FORMAT([COLOR=#333333][FONT=Arial]Worksheets("Codes").Range("[/FONT][/COLOR]<wbr style="color: rgb(51, 51, 51); font-size: small; background-color: rgb(250, 250, 250); font-family: Arial, Helvetica, sans-serif;">[COLOR=#333333][FONT=Arial]A50").value[/FONT][/COLOR],"m-d-yyyy") & ".pdf"

 

[Joe4]

But if you try to save the file with the slash (/) it will send a filename error, you should use hyphen (-).

Excellent point. Cannot believe I overlooked that!

 

[bobkap]

MEGA thanks!! That did it. I hadn't declared the variable out of just being too lazy. Once I did either that, OR your other line of code, it worked like a charm!!

 

[DanteAmor]

MEGA thanks!! That did it. I hadn't declared the variable out of just being too lazy. Once I did either that, OR your other line of code, it worked like a charm!!

I'm glad to help you. Thanks for the feedback.