Macro to change date as per business days and its format daily in a column

[galbatrox9]

Hey guys!

Well i have this excel macro that im working on which will automate a lengthy process.

The macro currently does this : Takes raw data, changes its date daily and makes it in mm/dd/yyyy format, create 7 different excel files and save them in a location.

Now i have everything working, but this darn date issue wont fix. Sometimes it works but sometimes it fails.

The issue is I have to run the excel file in a remote desktop virtual machine and then after the output files are made, copy them into another remote desktop VM and use it there. It midst all this, the files get buggy and the dates dont come out right.


Here is how the date comes in the raw file. https://www.dropbox.com/s/r114ucp8kg...lpme.xlsx?dl=0

This excel file just has the date column of my file.

Now here is the function i recorded in my macro on this column to get my desired mm/dd/yyyy format, but sometimes what happens is, they get converted into random 45123 numbers and sometimes it doesnt. I need a permanent fix for this. I recorded in my macro that it will go to general tab and change the column format to short date too, but that doesnt always work.

Please Login or Register  to view this content.

I use this code since i need to get the today's date -1 and it should only take business days. So if im using it on monday, i need friday.

Now i am a newbie and dont know much about codes and stuff, but i try to wing it.


Can someone help me out here?

[Journeyman3000]

The numbers that you are seeing is the conversion of the date to a long - i.e, the number of days after 1900 AD. You can convert them back from a long to a date:

Dim lngDateVal as long
Dim dDate as Date

lngDateVal = Date
ddate = lngDateVal

You can also format that long as a date.

Range("A1") = Format(lngDateVal, "mm/dd/yyyy")

The format of the cell may override the way the date is displayed though, which is probably why you are having problems. in that case, try this:

Range("A1").NumberFormat = "mm/dd/yy"