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.

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.

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.