Thread: [Solved] Excel 2013 – need help extracting date and time from text cell plus more.
View Single Post
 
Old 07-18-2014, 09:55 PM
macropod's Avatar
macropod macropod is offline Windows 7 32bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,363
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
You can extract the dates & times with a formaula like:
=--SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,FIND("/",A1)-1-(FIND("/",A1)>2),LEN(A1))),"AM"," AM"),"PM"," PM")
where the string you're processing is in A1. This returns a number which you can format as a date & time if you prefer.

As for the comparisons, are the data sorted by date & time? If so, the comparison requires nothing more than a simple subtraction using the results of the above formula.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote