Looking for a formula for time

jvisman

New Member
Joined
Apr 5, 2019
Messages
2
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
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 4, 2009
Messages
3,680
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
Joined
Apr 5, 2019
Messages
2
@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.
 

Watch MrExcel Video

Forum statistics

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