Working out time from days and hors worked

[henry]

Hi Guys,
I'm a novice to excel and am trying to make up a leave form. I have
column for time leave started (in hours) the next column is the dat
next column is time returned (in hours) then next column is dat
returned. I also have set up a list of public holidays. I then have
column to count the number of days taken as leave usin
=networkdays(startday, endday, holidays). This is fine if people ar
taking off whole days, but can anyone suggest how I can take int
account people who will insist on taking half day leave (which is why
have the hours columns)?

Cheers
Henr

 

[mangesh_yadav]

A1 = start time
B1 = start day
C1 = end time
D1 = end day

Use the formula in E1:
=NETWORKDAYS(B1,D1)+(C1-A1)*24/8

In the above formula 24 is the number of hours in a day. And 8 is the
number of working hours in a working day. Please change the '8' if this
is different in your case.

Example
Start time, Start day, End time, end day, Days leave
13:00, 5/31/2005, 9:00, 6/5/2005, 3.50
13:00, 5/31/2005, 17:00, 6/5/2005, 4.50

Mangesh

 

[henry]

Thanks. It is really so easy when you are shown. Our work day is fro
8:30 to 4:40 with 45 mins for lunch so i think my easiest is to conver
hrs to minutes or it'll end up rather confusing.
Thanks again

 

[mangesh_yadav]

Maybe you could use some thing like:
=NETWORKDAYS(B1,D1)+(C1-A1)*24/(8+10/60)
for 8:10 workday

And to exclude the lunchbreak,
=NETWORKDAYS(B1,D1)+(C1-A1)*24/(7+25/60)

Manges

 

[William Benson]

My attempt - assumes 8AM starting time, and an 8 hour day, and rounding to
nearest hour, then total to nearest 0.1 Day:

Goal: multiply networkdays() x 8, and remove from this result the number of
hours (subject to 0-8 constraint) worked on the day leaving and the day
returning. You have to assume they are not leaving or returning on a weekend
or a holiday, but if so, there's a problem and the solution will be much
more complicated. Like, partially worked weekends wouldn't mean anything but
half a holiday might ... My old employer did not pay people for partially
taken holidays, solved THAT problem.

Range Formula
Time_Left 9:00:00 AM
Day_Left 6/1/2005
Time_Returned 3:00:00 PM
Date_Returned 6/2/2005
Wrked_Day_left
=IF(HOUR(Time_Left)-8>8,8,IF(HOUR(Time_Left)-8<0,0,HOUR(Time_Left)-8))
Wrked_Day_Arr =8 -
IF(HOUR(Time_Returned)-8>8,8,IF(HOUR(Time_Returned)-8<0,0,HOUR(Time_Returned)-8))

Outage
=INT((networkdays(Day_Left,Date_Returned,Holidays)*8-Wrked_Day_left-Wrked_Day_Arr)/8
*10)/10
Note: rounded to nearest 0.1 Day

Holidays The range of holidays

 

[William Benson]

I am not trying to party-poop, but this solution does not take into account
that the networkdays will count both the day they left and the day they
returned as absent days.

Thus you need a deduction, as a % of 1 day, for hours worked on either
end-point.

Bill

"mangesh_yadav" <[email protected]>
wrote in message
news:[email protected]...

 

[Mangesh Yadav]

Hi William,

Thanks for pointing out. Infact I did not realise this, was concentrating
more on the difference in time calculation.

Mangesh