Hello. I'm trying to add the hours in a schedule created in an excel spreadsheet. The information is formatted as "8am to 5pm" in each cell (varies of course depending in the individuals schedule). What I would like to do is translate anything with a "pm" into a 2400 format and then subtract the smaller number to get the total hours (ie 5pm becomes 1700, 8am becomes 800). I want to add up the hours of each day for the whole week but I think once I understand how to pull the hours per cell I can figure out how to do an array. Is there any suggestions for this?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Extract hours from a string text.
- Thread starter jrschenck
- Start date
Ron Coderre
MrExcel MVP
- Joined
- Jan 14, 2009
- Messages
- 2,316
This is just a general comment:
I hope you have some controls in place to prevent users from "free forming" the data entry.
I'm thinking of preventing entries like the following:
8:15 a.m. thru 5 PM
9 to 5
etc
I hope you have some controls in place to prevent users from "free forming" the data entry.
I'm thinking of preventing entries like the following:
8:15 a.m. thru 5 PM
9 to 5
etc
Upvote
0
Ron, I realize that would be easier, but this is a table that I inherited. It is what it is. I don't have a lot of control over it and doubt if I can retrain the people to input differently. I can calculate this in my head, but thought it would be an interesting challenge to see if I can write a program that will strip the numbers, calculate the hours and eventually add them up for the week. However I don't have a lot of time to put into this and need some help to get started. Thank you.
Upvote
0
Ron, I realize that would be easier, but this is a table that I inherited. It is what it is. I don't have a lot of control over it and doubt if I can retrain the people to input differently. I can calculate this in my head, but thought it would be an interesting challenge to see if I can write a program that will strip the numbers, calculate the hours and eventually add them up for the week. However I don't have a lot of time to put into this and need some help to get started. Thank you.
Upvote
0
Ron Coderre
MrExcel MVP
- Joined
- Jan 14, 2009
- Messages
- 2,316
The optimal situation would be to change the input tab so it has controls on what is allowable (maybe a data validation).
If that can't be done, we'll need samples of every kind of format that you plan on parsing.
If that can't be done, we'll need samples of every kind of format that you plan on parsing.
Upvote
0
