generic numbers for time converted to hours?

BBezooyen

New Member
Joined
Sep 26, 2009
Messages
7
Hi there.

I am working on a schedule to work with an hourly budget. I was wondering if there is a way to type in hours for an employee to work and have it totaled at the bottom of the column for what day is worked.

<TABLE style="WIDTH: 409pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=545 border=0 x:str><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl88 style="BORDER-RIGHT: black 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; WIDTH: 409pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=545 colSpan=8 height=22>Schedule</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Date</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>10</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>11</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>12</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>13</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>14</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>15</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>16</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18></TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent">Sun</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent">Mon</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent">Tues</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent">Wed</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent">Thurs</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent">Fri</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent">Sat</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18> </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl80 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Employee</TD><TD class=xl91 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">12-8</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl82 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Employee</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">9-5</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">11-730</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl82 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Employee</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl82 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Employee</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 8-4</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2-1030</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl82 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Employee</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">12-8</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl84 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD><TD class=xl84 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>Total hours:</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent" x:num>8</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent" x:num>8</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent" x:num>16</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent" x:num>8.5</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent" x:num>8.5</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent"> </TD><TD class=xl86 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

This is an example of what I mean. The problem I run into is I am unable to tally up all the hours or create a formula for excel to understand that 12-8 is 8 hours. I would really prefer to not have to type in the actual time either. (8:00AM-4:00PM, 12:00PM-8:00PM)

Any help is greatly appreciated!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
No easy answer I can see. We'd have to both account for times that span the noon border - so, somehow, Excel needs to know that 9-2 is (5 hours) and not (-7 hours). Also, the presence of 1030 means we need to parse out the quarters -

So we need to parse out two time, determine if the latter is AM or PM, then figure out if its a full hour or an hour that ends in 30, and then finally do the subtraction to get the duration, and add it all up...

In other words ... too much work for a simple schedule of this kind-it even begins to look easier just to add the numbers up in your head. the problem as I see it is that you'd end up with a really massive formula to cover all these details - or you'd have to write some vba to embed the logic behind the worksheet.

You might like to do something like:

9-2 (5)
2-4:30 (2.5)
9-2 (5)


We could add up the numbers in parenthesis ...

Or, put the start and stop in separate cells, with real time formatting.
9:00AM | 2:00PM
2:00PM | 4:30PM
9:00AM | 2:00PM


With a little smart formatting, that will still look good on paper - and it would be a cinch to add it up. I can't help but thinking that from the standpoint of number crunching the format you desire is the least desirable (though it may be how we'd write this on paper).

I'd love to see someone come up with something really smart here ... maybe if there's specific rules that can be counted on we'd be able to reduce the amount of logic needed for parsing this out (for instance, if shifts never start after 7PM, we'd know that any 8 ,9, 10, or 11 is AM and not PM ... etc). And do we ever start on the 15's? Even with these helps, though, I'm imagining the formula to still be awfully long - maybe in a vba function we could hide some of that.

Alex
 
Last edited:
Upvote 0
I'd love to see someone come up with something really smart here
I'm not sure this falls into that category...

**If** they enter the time in 24 hr format **AND** the times will not span past midnight...

For example:

7:00 AM to 1:35 PM is entered as 700-1335

Array entered** :

=SUM(IF(ISNUMBER(FIND("-",A1:A5)),TEXT(MID(A1:A5,FIND("-",A1:A5)+1,4),"00\:00")-TEXT(LEFT(A1:A5,FIND("-",A1:A5)-1),"00\:00")))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Format as [h]:mm
 
Upvote 0
**AND** the times will not span past midnight...
Eh, come to think of it, that shouldn't be too hard to deal with.

=SUM(IF(ISNUMBER(FIND("-",A1:A5)),MOD(TEXT(MID(A1:A5,FIND("-",A1:A5)+1,4),"00\:00")-TEXT(LEFT(A1:A5,FIND("-",A1:A5)-1),"00\:00"),1)))

Note that 12:00 AM needs to entered as 0000 (although just 0 will work).
 
Upvote 0
I think so :)

I was thinking of using military time yesterday, as I mulled this one over -- I guess its up to the OP if a slightly different format for the times is okay (ie, 700-1330 for 7-130).

I sympathize with the post - I worked a few years in retail and we had a shift schedule about exactly like this.

Alex
 
Upvote 0
I appreciate all your support so far!

I am sure i can live with the 24h time. Is there any way that we can change a number such as 384 to be 384:00. It would be simple if the cell was not part of a formula.

An example is:
we work out out budget of hours to alocate 384, now using the formula you already helped with we get a time frame that i would like to subtract the two to show the difference of hours remaining to allocate.


I hope i explained it properly...
 
Upvote 0
If you want the result of the above formula to be in decimal format just multiply by 24.

=SUM(IF(ISNUMBER(.......)*24

Format as General or Number
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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