TimeSheet --> subtracting Time and Hours

  • Thread starter Thread starter Jan G. Thorstensen
  • Start date Start date
J

Jan G. Thorstensen

Hi.
I am working on a Time sheet where the business is running 24hrs a day.
For working hours from 22:00 hrs until 06:00 is better paid than the other
working hours.

I want theese extra paid hours to show in one separate coloumn and wonder if
someone have a good Formula do to this. Below are some sample data;

StartTime EndTime ElapsedHrs NumOfHrsFrom22To06
06:00 09:00 03:00 00:00
23:00 03:00 04:00 04:00
02:00 09:00 07:00 04:00
19:00 03:00 08:00 05:00


I have tried to use some formula Example from Chip Pearson's site.
This looks like
=(B4-A4+(B4<A4))
This works just fine for the elapsed hours no matter if the Start Time is
later
than the End Time, because the time has passed midnight.

The problem is in the last Column; "NumOfHrsFrom22To06" where I want only
the hours between 22:00 and 06:00. (Or hours between 10PM and 06AM).

I have tried; (OUT=06:00, INN=22:00)

=(STØRST(OUT;MIN(B5;OUT))-STØRST(A5;MIN(A5;INN))+(B5<A5))
and this work if the Start Time is less than the End Time i.e. 02:00 to
09:00 but it does not
work if the Start Time is later than the End Time i.e. 23:00 to 05:00.

A working formula for this purpose would be very much appretiated. Thanks.

Jan
 
Hi Jan,

Assuming
A1: Startime
B1: EndTime
H1: 6:00
J1: 22:00

The working hours from 22:00 until 6:00:
=MAX(0,MIN(B1,$H$1)-IF(B1>A1,A1,MIN(0,A1-$H$1)))+MAX(0,
1-MAX($J$1,A1)-IF(B1>A1,1-B1,MIN(0,$J$1-B1)))

Regards,

Daniel M.
 
Note 1:
Format the results as [h]:mm

Note 2:
If your StartTime equals EndTime, current formula assumes you will have done a
24 hours shift and report 8:00 (from 22 to 6).
If you want to report 0 in those circumstances, replace ">" with ">=" in the
formula.

Regards,

Daniel M.
 
Daniel.M

Sorry, this don't work for me. If the worker starts at 02:00 and ends at
04:00,
then the fomula compute correctly 2 hrs because these two hrs are in the
range
22:00 - 06:00. But what if the worker starts at 23:00 and ends at 09:00, the
next
morning. Your formula returns 13 hrs wich is wrong. It should of been one
hrs
(before midnight) + 6 hrs (after midnight), a total of 7 hrs with special
hourly rates.

Jan
 
Jan,
But what if the worker starts at 23:00 and ends at 09:00, the
next morning. Your formula returns 13 hrs wich is wrong.
It should of been one hrs (before midnight) + 6 hrs (after
midnight), a total of 7 hrs with special hourly rates.
Click to expand...

My formula returns 7:00 as expected.
Please verify if you copied it correctly.

Regards,

Daniel M.

P.S.: Sorry if you receive multiple copies of that answer.
I just don't see it appearing on the NG.
 
Daniel M.
Super duper! Thanks!! It works...
It was me that had mixed cell values for $H$1 and $J$1.

This works great, thanks a lot.

Regards,

Jan
 
Back
Top