Looking for a formula for time

[jvisman]

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.

 

[Yongle]

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 worked	clock 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

 

[James006]

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 :

=B3+((((40*60)-(A2*60))-((MOD(C2-B2,1)*24)*60))/1440)

Hope this will help

 

[jvisman]

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

 

[James006]

Glad you have managed to fix your problem