Time Calculations

[meighkee]

Greetings,

I am designing a time sheet and am trying to do some simple math with 'custom' formatted cells.

For example: I have formatted A1 and B1 with the custom format:
00":"00.
Therefore when I type in, say 0600 (for a start time) in A1, and 1430 (for an end time) in B1, the time displayed in those cells is 06:00 and 14:30 respectively. This makes it quick and easy to type in the scheduled hours for an employee.

Now the problem I'm having is doing some math with these cells. What I would like to do is subtract the start time from the end time for the hours worked and place that result in cell C1, as - I guess it would be decimal format - 8.50, rather than 8:30.

I would really appreciate some help, thank you.

Michael

 

[SteveO59L]

Why cant you format the cells as hhh:mm ?

 

[AdamL]

Maybe try

=INT(B1/100)+MOD(B1,100)/60-INT(A1/100)-MOD(A1,100)/60

-----------
edit

Why cant you format the cells as hhh:mm ?

I'm guessing to avoid having to type the colon during data entry.

 

[meighkee]

Steve059L -
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks, I tried that but when I input the time into a cell that's formatted as hhh:mm, it displays 00:00.
<o> </o>
Thanks again,
~~~~~~~~~~
<o> </o>
AdamL -
<o> </o>
That formula appears to be working great. There may be an occasion when the hours worked cross the midnight hour. Ex: 10:00 - 05:00, and I noticed when I use the formula you suggested, I get a negative number, possibly because the end time then becomes a smaller number than the start time. Is there a solution for that or will that be something we'll just have to work around?
<o> </o>
Thanks again to both of you for your help.
<o> </o>
Michael
ps Yes, you're right, the idea is to avoid having to type in the colon as it can be quite time consuming when you have a lot of employees.

 

[meighkee]

Greetings once again,

Again, thank you for your help. I do have one additional request. I'm experimenting with the "ISNUMBER" function to have the end result of my example ignore any text that may be in the 'start time' or 'end time' cells.

For example:
Using the following sample of my time sheet -

Monday
A1 = 0600
B1 = 1430

Tuesday
A1 = 0600
B1 = 1430

Total Hrs
C1 = 17.00

If cell A1, or B1, were to read "OFF", rather than have a start time in it I would like C1 to ignore that and return the results of the remaining days rather than display - #value!
Can the following formula, suggested in this thread, be modified to ignore any text that may be in a cell?

=INT(B1/100)+MOD(B1,100)/60-INT(A1/100)-MOD(A1,100)/60

Thanks very much,
Michael

 

[meighkee]

Ooops,

With reference to that last request. In my sample the cells that fall under Tuesday should read A2 and B2.

Thanks,
Michael