Time Calculations

meighkee

New Member
Joined
Sep 3, 2008
Messages
39
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

AdamL

Well-known Member
Joined
Sep 25, 2008
Messages
767
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.
 
Last edited:

meighkee

New Member
Joined
Sep 3, 2008
Messages
39
Steve059L -
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Thanks, I tried that but when I input the time into a cell that's formatted as hhh:mm, it displays 00:00.
<o:p> </o:p>
Thanks again,
~~~~~~~~~~
<o:p> </o:p>
AdamL -
<o:p> </o:p>
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:p> </o:p>
Thanks again to both of you for your help.
<o:p> </o:p>
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

New Member
Joined
Sep 3, 2008
Messages
39
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

New Member
Joined
Sep 3, 2008
Messages
39
Ooops,

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

Thanks,
Michael
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,539
Members
414,246
Latest member
allyciv

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
Top