Altering the existing macro to get date as numerical va;ue

Hi everyone,

I use a Macro (code attached) to flatten my dataset for dates (From vertical to Horizontal). Sample file is attached. However, in the resulting file dates appear as text and therefore, does not allow me to upload and use it with other softwares like SPSS for further analysis. Can someone please help me change the Macro code so that in the resultant file I get dates as numerical?

Thank you

Please Login or Register  to view this content.

Not sure what you mean by "dates as numerical". Your problem may be easily fixed by switching the cell formatting away from your current custom setting and to the date format you desire. In other words, I don't think the vba code is the problem, I think it's the cell "Number" formatting.

Try this...

Please Login or Register  to view this content.

The Replace function coerces the text-dates into serial-dates.

Hi Gyclone,

I want dates as numbers and not as text. The current problem is after using the Macro, my dates get converted into text. In this new attachment labelled as "Attendance after Macro" , I have used Macro to make it more clear. Column M, which is highlighted shows that the dates are not read as numbers now. Therefore, I cannot use them for any calculations because they are treated as string variable in SPSS.

Regrading manual formatting: I tried using format option. It is feasible to use it with small number of cases. However, my problem with that is that I have huge dataset like 20,000 cases and 600 columns. It takes a lot of time to format every cell and if used together, the excel crashes every time.

Thanks,
Ajang

Hi ajang,

I see from your spreadsheet what format you want, but, just so you'll understand my question, there are many, many ways that dates can be displayed as numbers (1/2/2013, 2013/1/2, 01/02/2013, 01-02-13, etc., etc.). In the future, you could save a step by including the actual format you want in your original post.

As for how to accomplish it, you need to select the appropriate range and change the number format, which you can do via vba (you don't have to do it manually).

Please Login or Register  to view this content.

Try this

Please Login or Register  to view this content.