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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]hours worked[/td][td]clock out[/td][td] Formula copied down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

32.68​

[/td][td]

16:18​

[/td][td] =(MIN(((A2<36)+(40-A2)+8),17)-1/60)/24[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

35.61​

[/td][td]

13:22​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

38.49​

[/td][td]

09:29​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

36.76​

[/td][td]

11:13​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

38.79​

[/td][td]

09:11​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

33.83​

[/td][td]

15:09​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

31.61​

[/td][td]

16:59​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

36.70​

[/td][td]

11:17​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

38.16​

[/td][td]

09:49​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

33.90​

[/td][td]

15:05​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

33.99​

[/td][td]

14:59​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

36.00​

[/td][td]

11:59​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

32.00​

[/td][td=bgcolor:#E2EFDA]

16:59​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

31.50​

[/td][td=bgcolor:#E2EFDA]

16:59​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

30.00​

[/td][td=bgcolor:#E2EFDA]

16:59​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

20.00​

[/td][td=bgcolor:#E2EFDA]

16:59​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

0.00​

[/td][td=bgcolor:#E2EFDA]

16:59​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[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