Calculate automatic overtime according to times, by time registration.

Johnny Harrang 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,649 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viki Ji_MSFT 4,421 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))))

    152892-image.png

    ----------

    Update：

    Please check the following formulars:

    • 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","")))

    153521-image.png

    Hope the information is helpful to you.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.