Counting Hours in a Schedule

mike4777

Board Regular
Joined
Apr 12, 2011
Messages
122
I was wondering if someone could help me develop a formula to count the hours when formatted in such a way:

NameHoursMondayTuesdayWednesdayThursday
Name 1=Formula8am-8pm10:30pm-6:15ametcetc
Name 2
Name 3
etc

<tbody>
</tbody>

I can do it with a million LEft( and Right( but that seems terribly clunky and prone to possible error.
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I was wondering if someone could help me develop a formula to count the hours when formatted in such a way:

NameHoursMondayTuesdayWednesdayThursday
Name 1=Formula8am-8pm10:30pm-6:15ametcetc
Name 2
Name 3
etc

<tbody>
</tbody>

I can do it with a million LEft( and Right( but that seems terribly clunky and prone to possible error.

Well, me thinks that the way you have the "-" between the times makes it impossible to get at the values without using left and right functions. Using said functions isn't as clunky as the way you are setting up the table.

I'd set it up like this, personally:

Set up the column header for the days by putting the Day of the Week in a cell and the merge the adjacent cell to the right. Now the day of the week header is over two columns. Then enter one time in the left cell under the day of week header and the other time in the right cell of the day of week header.

Formula would be easy then: =(D2-C2 + E2-F2 + H2-G2) * 24, etc
 
Upvote 0
The problem with that is it has to have pm am in. I managed to pull this off before years ago but I forgot how I did it.
 
Upvote 0
I'm also working with :
and I'm not sure how to have it differentiate between a single digit time and double digit time
 
Upvote 0
The problem with that is it has to have pm am in. I managed to pull this off before years ago but I forgot how I did it.
The AM/PM won't bother anything. It's just a formatting option.
 
Upvote 0
I was wondering if someone could help me develop a formula to count the hours when formatted in such a way:

NameHoursMondayTuesdayWednesdayThursday
Name 1=Formula8am-8pm10:30pm-6:15ametcetc
Name 2
Name 3
etc

<tbody>
</tbody>

I can do it with a million LEft( and Right( but that seems terribly clunky and prone to possible error.



NameHoursMondayTuesdayWednesdayThursday
Name 119.758am-8pm10:30pm-6:15ametcetc
Formula in B2=(MOD(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,LEFT(C2,FIND("-",C2)),""),"am"," am"),"pm"," pm")-SUBSTITUTE(SUBSTITUTE(LEFT(C2,FIND("-",C2)-1),"am"," am"),"pm"," pm"),1)+MOD(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,LEFT(D2,FIND("-",D2)),""),"am"," am"),"pm"," pm")-SUBSTITUTE(SUBSTITUTE(LEFT(D2,FIND("-",D2)-1),"am"," am"),"pm"," pm"),1))*24
Try this..
<colgroup><col width="173" style="width: 130pt; mso-width-source: userset; mso-width-alt: 6326;"> <col width="110" style="width: 83pt;"> <col width="185" style="width: 139pt; mso-width-source: userset; mso-width-alt: 6765;"> <col width="179" style="width: 134pt; mso-width-source: userset; mso-width-alt: 6546;"> <col width="110" style="width: 83pt;" span="5"> <tbody> </tbody>
 
Upvote 0
Don't bother with a formula for this data structure. Separate out your time into separate fields so that you have only the time value in that cell. Then just do a SUM or SUMIF (in case you have conditions). Otherwise you'll end up with a monstrosity like vogel997 posted (no offense, and untested).
 
Upvote 0
The long formula does not work. It returns a value error. I broke up Friday time into two sections with 12:00pm (in cell d2) and 8:00pm (in cell f2). e2 = "-"

I don't mind a long formula as long as it works. I cannot simply sum because I have am and pm. I have a formula that breaks down the numbers but now I don't know how to get the difference between them. Subtracting does not work because

12:00pm - 8:00pm = 4 and not 8 hours.
 
Upvote 0
Tryf2-d2
<colgroup><col width="173" style="width: 130pt; mso-width-source: userset; mso-width-alt: 6326;"> <col width="110" style="width: 83pt;"> <tbody> </tbody>
 
Upvote 0
like I said, that does not work for a multitude of reasons listed above.

12pm-8pm = 4 hours (where it should be 8 hours)

also you cannot subtract a value with text on it
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top