# Calculate automatic overtime according to times, by time registration.

1 Reputation point
2021-11-25T09:55:16.87+00:00

I have a spreadsheet I found on the internet but the owner of the spreadsheet does not respond to inquiries.

I have another spreadsheet I made myself for keeping overtime hours. Here I enter the date, start time and end time and the spreadsheet then calculates the number of hours between the times. But I want to define from 15:30 - 21:00 it must calculate 50% overtime and from 21:00 - 08:00 it is 100% overtime, and for weekends from Friday 21:00 to Monday morning at 08:00 it is 100% overtime.

The owner of the spreadsheet I have found has the solution to this by using this formula which is written in Norwegian: =@timeantall(\$D3;\$E3;16;21;\$C3;Fre).

Here it calculates how many hours there are between time, but in addition it calculates how many 50% hours are between 1600 and 2100, and in addition only on Friday. There are several variables regarding the periods and days.

I do not find anything about how Excel can calculate this and assume that it is in a macro or programmed in VB. I do not have access to macros as the owner has password protected this part.

Does anyone understand what I am trying to explain and possibly have a solution?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,615 questions

1. 4,401 Reputation points
2021-11-26T09:19:43.027+00:00

Hi @Johnny Harrang ,
Welcome to Q&A forum!

According to your description, the following formula is just a reference, you can change it according to your needs. In my test case, the working hours are 8:00 am-18:00 pm from Monday to Friday.

`=IF(AND(WEEKDAY(E2)>=6,WEEKDAY(F2>=6)),"100%",IF(DAYS(F2,E2)=0,IF(AND(HOUR(E2>=8),HOUR(F2)<=18),"100%",ROUND((MOD(F2,1)-"18:00")/(F2-E2)*100%,1)),IF(AND(HOUR(E2)>=18,HOUR(F2)<=8),"100%",ROUND(("6:00"+MOD(F2,1))/(F2-E2)*100%,1))))`

----------

Update：

• E2: `IF(I2<=5,IF(AND(("15:30"-C2)>=0,("15:30"-D2)>=0),D2-C2,IF(AND(("15:30"-C2)>=0,("15:30"-D2)<0),"15:30"-C2,"")),"")`
• F2: `IF(I2<=5,IF(AND(("15:30"-C2)>=0,("15:30"-D2)>=0),"",IF(AND(("15:30"-C2)>=0,("21:00"-D2)<0),"21:00"-"15:30",IF(AND(("15:30"-C2)>=0,("21:00"-D2)>=0),D2-"15:30",IF(AND(("15:30"-C2)<0,("21:00"-C2)>=0,("21:00"-D2)>=0),D2-C2,IF(AND(("15:30"-C2)<0,("21:00"-C2)>=0,("21:00"-D2)<0),"21:00"-C2,""))))),"")`
• G2: `IF(I2>5,D2-C2,IF(AND(("21:00"-C2)<0,("21:00"-D2)<0),D2-C2,IF(AND(("21:00"-C2)>=0,("21:00"-D2)<0),D2-"21:00","")))`