# Looking for a formula for time

#### jvisman

##### New Member
I want to make a timecard calculator that tells you what time you need to clock out so an employee does not work over 40 hours in a week. Also the timecard displays time in a 100 minute clock. (Example)
Employee worked 32.68 hours by Thursday, start time on Friday is 8:00am, lunch out is 11:59am, and lunch in is 1:01pm. Formula should let employee know what time to punch out.

Any help is greatly appreciated, Thanks.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Yongle

##### Well-known Member
Welcome to the forum

format the cell with required time format

to do the basic calculation
=((A2<36)+(40-A2)+8)/24

but is 17:00 latest clockout?
=MIN(((A2<36)+(40-A2)+8),17)/24

your question suggests that clocking out is on the minute before ( eg lunch out is 11:59am)
=(MIN(((A2<36)+(40-A2)+8),17)-1/60)/24

Excel 2016 (Windows) 32 bit
A
B
C
1
hours workedclock out Formula copied down
2
32.68​
16:18​
=(MIN(((A2<36)+(40-A2)+8),17)-1/60)/24
3
35.61​
13:22​
4
38.49​
09:29​
5
36.76​
11:13​
6
38.79​
09:11​
7
33.83​
15:09​
8
31.61​
16:59​
9
36.70​
11:17​
10
38.16​
09:49​
11
33.90​
15:05​
12
33.99​
14:59​
13
36.00​
11:59​
14
32.00​
16:59​
15
31.50​
16:59​
16
30.00​
16:59​
17
20.00​
16:59​
18
0.00​
16:59​
 Sheet: Sheet1

Last edited:

#### James006

##### Well-known Member
Hello,

With your decimal Hours in cell A2 : 32.68

and your respective Times in cells B2 and C2: 8:00 and 11:59

and in cell B3: 13:01

you can have in cell B4 :
Code:
=B3+((((40*60)-(A2*60))-((MOD(C2-B2,1)*24)*60))/1440)

Hope this will help

#### jvisman

##### New Member
@James006
I used your formula and it works the same as I was previously using as
=(40-A2)/24+B3-(C2-B2)
The problem is sometimes it's off by 1 minute when A2 ends in .27 or .02 for example.
.......however I put a Round function in with (A2*60) and it solved the problem.
New formula
=B3+((((40*60)-ROUND((A2*60),0))-((MOD(C2-B2,1)*24)*60))/1440)
Thank you for the help.

Replies
1
Views
185
Replies
31
Views
631
Replies
24
Views
1K
Replies
2
Views
270
Replies
7
Views
488

1,130,041
Messages
5,639,710
Members
417,106
Latest member
rbahena

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