Counting Hours in a Schedule

[mike4777]

I was wondering if someone could help me develop a formula to count the hours when formatted in such a way:

Name	Hours	Monday	Tuesday	Wednesday	Thursday
Name 1	=Formula	8am-8pm	10:30pm-6:15am	etc	etc
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.

 

[mike4777]

btw, I moved the column out super far and it still displayed ##################################################################################################

 

[mike4777]

I tried this on another sheet and 12:00 am - 12:30 am with the first mod formula you gave me returns 23:30???

 

[Br0ck]

Times that are negative will result in the: ##########.

 

[mike4777]

It wasn't negative that I know of... though for some reason it is working now. I just don't know what to do with a formula that returns 23:30

 

[Zack Barresse]

You can't upload attachments to the forum.

Check out this example on my OneDrive account:
http://1drv.ms/1pA952d

EDIT: The reason you're getting the pound signs (negative time values) is that you're taking the START time minus the END time. You need to take the END time minus the START time. Higher number minus the lower number. A value of 23:30 is 23 and a half hours. Like if you worked 8:00 AM until 7:30 AM the next day.

 

[mike4777]

click on the link - it goes to another excel forum.

 

[mike4777]

Here is my excel sheet: https://onedrive.live.com/redir?resid=F06FF57FBCF74AA!450&authkey=!AKHJrK5gAOvNF-I&ithint=file,.xlsx

 

[mike4777]

I looked at your file but it doesn't really tell me anything different than what you already said. I formatted it similar. The formula I put in is returning 23:30

 

[Zack Barresse]

Should the formula go into cell C4 (the highlighted cell)? And should it calculate for Saturday through Thursday?

 

[Zack Barresse]

I amended your file. Here is the formula:

=IF(COUNTA(G4,I4)=2,(I4<=G4)+I4-G4,0)
+IF(COUNTA(J4,L4)=2,(L4<=J4)+L4-J4,0)
+IF(COUNTA(M4,O4)=2,(O4<=M4)+O4-M4,0)
+IF(COUNTA(P4,R4)=2,(R4<=P4)+R4-P4,0)
+IF(COUNTA(S4,U4)=2,(U4<=S4)+U4-S4,0)
+IF(COUNTA(V4,X4)=2,(X4<=V4)+X4-V4,0)

This checks if there is a start and end value in each cell, then calculates the difference. Output column (assumed to be your highlighted cell column) formatted as "[h]:mm". Is this what you're looking for?