I have an excel spreadsheet that has a multitude of dates and times, and I need to calculate the elapsed time between two separate columns in minutes.
The problem is, the cells are documented in a format that excel doesn't recognize as a time.
They are in the following format:
11/8/15 2120 (which correlates to 9:20PM on 11/8/15)
If I add a colon to the entry so that it would show 11/8/15 21:20, then I can easily calculate the elapsed time with =(B1-A1)*24*60 to get my result in minutes. However, I don't want to spend hours adding a colon to every cell.
I am not very familiar with Macros. Is there a way I can make a Macros to add a colon as the 3rd to last character in a cell? Or is there a way to show excel that this is a time via formatting the cells?
Short of manually adding a colon, every iteration I've tried with formatting has led to a "#VALUE!"
Bookmarks