I am trying to sort some data out in excel. I had two columns with two different dates in a table. I have calculated the difference in days and it appears as follows:
LOS 5days 20days 20days 8days 27days 13days
What I would like to do is convert the “5days” in to a numerical didgit “5” but I can not figure out how to do it. Can anyone help?
Thanks
Do a find and replace: http://video.about.com/spreadsheets/Find-and-Replace-in-Excel.htm
.
Assuming your data is in column A, starts in row 1, and is always the same in that it is always 5days, 12days, 23days, etc…in a new column input the following formula
=REPLACE(A1,LEN(A1)-3,4," ")
Copy that down and now you have a column of numerical data that can be analyzed. If you want to get rid of the formulas just do a copy/paste values over the same column.
Hope that makes sense.
You could have used datedif to calculate the date difference for you.
For example if A1 has a date and B1 has a date, the difference in days is:
=datedif(a1,b1,“d”)
Search the help or google for other things you can use instead of “d” for days.
Why don’t you just subtract the later date column from the earlier date column, and format as a number?
You can change the format on the cell with the format cells dialog (from the menu, F_ormat → C_ells, or just Ctrl+1).
A6: 13days
C6: =LEFT(A6,LEN(A6)-4)
result: 13
.