Timesheet, overtime with daytime from 7:30am to 16:30pm
 

I´m trying to make a timesheet, so far I have total hours but the daytime
hours are from 7:30am to 16:30pm. Is there a formula that counts the hours
worked within this time and put´s that number in one cell and puts extra
hours in another cell.
I know that this might be hard to find out, couse I want to have it easy for
me to use, it would be best if I could enter the starttime and endtime for
each day and excel would calculate the rest.

Timesheet, overtime with daytime from 7:30am to 16:30pm
 

Let's says
Start time in A1, end time in B1, regular hours in C1, and OT hours in D1

Regular hours
C1 =MIN((B1-A1)*24,8)

OT hours
D1 =MAX(0,(B1-A1)*24-8)


"Arkon" wrote:

I´m trying to make a timesheet, so far I have total hours but the daytime
hours are from 7:30am to 16:30pm. Is there a formula that counts the hours
worked within this time and put´s that number in one cell and puts extra
hours in another cell.
I know that this might be hard to find out, couse I want to have it easy for
me to use, it would be best if I could enter the starttime and endtime for
each day and excel would calculate the rest.

Timesheet, overtime with daytime from 7:30am to 16:30pm
 

I tried the max formula and if endtime is less then 16:30 then I get a
negativ number, howerver I did not have *24-8
And also what will the outcome be if starttime is 8:00 with this formula?


"Teethless mama" wrote:

Let's says
Start time in A1, end time in B1, regular hours in C1, and OT hours in D1

Regular hours
C1 =MIN((B1-A1)*24,8)

OT hours
D1 =MAX(0,(B1-A1)*24-8)


"Arkon" wrote:

I´m trying to make a timesheet, so far I have total hours but the daytime
hours are from 7:30am to 16:30pm. Is there a formula that counts the hours
worked within this time and put´s that number in one cell and puts extra
hours in another cell.
I know that this might be hard to find out, couse I want to have it easy for
me to use, it would be best if I could enter the starttime and endtime for
each day and excel would calculate the rest.