# Time Calculations

#### meighkee

##### New Member
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### SteveO59L

##### Well-known Member
Why cant you format the cells as hhh:mm ?

##### Well-known Member
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
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>
<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

##### New Member
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
Ooops,

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

Thanks,
Michael

Replies
9
Views
69
Replies
8
Views
46
Replies
19
Views
230
Replies
3
Views
137
Replies
2
Views
67

1,127,500
Messages
5,625,162
Members
416,075
Latest member
TechJosh

### 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.

### Which adblocker are you using?

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

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