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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,045
Office Version
365
Platform
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,099,491
Messages
5,468,910
Members
406,620
Latest member
Gitani123

This Week's Hot Topics

Top