MS Excel 2007 Date format issue... Closed

Question

Hello,  
I have an XL spreadsheet with abt 15k rows, with a variety of data, the important ones being the dates - a start date and an end date. Based on the difference between these dates, I need to plan some actions, for which I'll be using IF conditions. 

Now, my problem is that all the dates are in a similar format "JAN 19,2011 23:42:16" in text format. This does not allow me to calculate the difference between the dates. 

I need the dates in the format "MM/DD/YYYY HH:MM:SS" so that I can calculate the difference between them. 

Would appreciate if anyone can help me either change the format automatically and then do the calculations or tell me a better way of doing the calculations using the existng values. 

Thx 
Sujesh 

Configuration: Windows XP / Internet Explorer 8.0

rizvisa1 · Answer

If all you want to do is convert into a format that is internally date to excel, then see if this thread helps

https://ccm.net/forum/affich-350882-cannot-change-the-date-format-in-excel-2007#1

If not, then could you please eloborate on your issue and could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc 
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.


Note:  your data need not be to be real data but a good representative of how data looks like

RWomanizer · Answer

hi Sujith,

follow the follwoing step. it will solve your problem.

1) write down 1 in any cell, now copy it,

2) select the date range

3) right click, now select paste special.

4) check the multiply and click ok.

5) now format your cell in the format. "MM/DD/YYYY HH:MM:SS" .

Regards,
Rahul

MS Excel 2007 Date format issue...

2 responses

Subscribe To Our Newsletter!