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.

  =IF(WEEKDAY(TODAY())=2,TODAY()-3,IF(WEEKDAY(TODAY())=1,TODAY()-2,TODAY()-1))​
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?