Hi everyone,
Could someone kindly assist with the following.
I have a bunch of opening times for several libraries, all working according to their own schedule.
The cells with their opening times look like:
9.30am-7.30pm
9.30am-7.30pm
10.00am-7.30pm
9.30am-7.30pm
9.30am-7.30pm
10.00am-3.45pm
If I want to extract starting time, I can use LEFT formula, but what about finishing time? How can I tell Excel to look for 7:30, and then covert it to 19:30? Please can someone help?
Thank you.
Last edited by bookharin; 03-23-2015 at 10:47 AM.
Assuming the Start time is always AM, and the End time is always PM..
Try this
Start Time: =SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("-",A1)-1),".",":"),"am"," am")+0
End Time: =SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,99),".",":"),"pm"," pm")+0
Format the cells with the formulas as a TIME.
Awesome. Thank you so much. xOriginally Posted by Jonmo1
Here's one way (there are many others):
=VALUE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,4),".",":")&":00")+IF(RIGHT(A1,2)="pm",0.5,0)
assuming your data starts in A1. Format the cell with the formula as a time in the style your prefer, then copy down.
Hope this helps.
Pete
You're welcome.
Thank you, @Pete_UK
Appreciate it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks