Format cell

bobkap

Active Member
Joined
Nov 22, 2009
Messages
323
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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"
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try this:
Code:
[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]
 
Upvote 0
Try this:
Code:
[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 (-).
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:

Code:
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"
 
Upvote 0
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!
 
Upvote 0
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!!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top