Menu
I have 100.000 rows of data in Excel. Some of the fields are dates, but the fields in Excel are as text. I need these fields in number format including both dates AND time (e.g. 21.10.2011 13:10:50). Formatting the cells doesn't work because that doesn't change the datatype. I can pick out the date and time with formulas but not get them in the same cell.
So what I am looking for is the formula to calculate the number representation of a date (the one you see if you format a date as a number).
731
Using strptime() , date and time in string format can be converted to datetime type. The first parameter is the string and the second is the date time format specifier. One advantage of converting to date format is one can select the month or date or time individually.
Select the cell or cells with serial numbers that you want to convert to dates. Locate the "Home" tab in the top right of the Excel window and click on it. Find the "Number Format" box in the toolbar that will usually say "General" or "Text." Click the down arrow next to the box and select "Date" from the menu.
Have you tried the =DateValue() function?
To include time value, just add the functions together:
=DateValue(A1)+TimeValue(A1)
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
