Hours to Days 1

[lars7]

Hi,
I have a column in my query for annual leave recorded in hours, I want to subtract this column from another that has the total yearly entitlement for annual leave in days, does any one know how I would do this.

 

[Remou]

How many hours annual leave make one day? What ever it is, multiply the total yearly entitlement by this number. Then you can subtract one from the other.

 

[lars7]

Hi again Remou,
Maths is not a strong point of mine (you may ask what is?) What I hope to end up with here is instead of having say 14.4 (1 Day = 7.2) that I have 2 so I can show this in a report next to the yearly entitlement. Would your suggestion lead to this?

 

[Remou]

No. But I thought it would be easier to compare say:
[tt]Annual Entitlement (hrs) Leave To Date (hrs)
144 14[/tt]
Rather than trying to deal with part days.

If however, annual leave in hours is always going to be a multiple of 7.2 hours, it should be easy enough to simply divide leave to date by 7.2.

 

[lars7]

Yes I understand, but I think I will have to deal with half days as well.

 

[Remou]

Well, here are a few thoughts:
[tt]LeaveHours = 115.5
HoursPerDay = 14.2
DaysToDate = LeaveHours \ HoursPerDay
HoursOver = LeaveHours - (DaysToDate * HoursPerDay)[/tt]

 

[lars7]

Hi Remou,
It's late here so I shouldn't attempt this untill morning. First thing I'll do is enter their leave entitlement in the staff table is hours and create a field with how many hours they work a day and then I will try your suggestion. Speak to you soon I'm sure.

 

[lars7]

Hi Remou,
Your suggestion has worked great. I added another column "ann/Leave" to subtract the A/L from the LeaveHours and then divided that by HoursPerDay. I haven't used the HoursOver yet but may still need it somewhere. I have what I want, 3 columns in the report A/L taken, A/l Entitled and A/L left all in days and it only took 2 queries, here is there SQL.

qryannualleavesub1

SELECT tblstaff.[Staff/Id], tblstaff.Employee, tblstaff.Hours, tblstaff.HoursPerDay, tblstaff.[Holiday/Ent], tblstaff.LeaveHours, Sum(tblrecords.AL) AS SumOfAL
FROM tblstaff INNER JOIN tblrecords ON tblstaff.[Staff/Id]=tblrecords.StaffId
GROUP BY tblstaff.[Staff/Id], tblstaff.Employee, tblstaff.Hours, tblstaff.HoursPerDay, tblstaff.[Holiday/Ent], tblstaff.LeaveHours;

qryannualleavesub2

SELECT qryannualleavesub1.[Staff/Id], qryannualleavesub1.Employee, qryannualleavesub1.[Holiday/Ent], IIf([leaveHours]-[sumofal] Is Null,[leaveHours],[leaveHours]-[sumofal]) AS [Ann/Leave], [Ann/Leave]/[HoursPerDay] AS DaysToDate, [Ann/Leave]-([DaysToDate]*[HoursPerDay]) AS HoursOver
FROM qryannualleavesub1;

As always thanks for everything.

 

[Remou]

I'm glad it is working out for you. The way I showed the calculation for days means that it shows whole days only, which may leave a few hours that have been taken that don't add up to a full day, hence HoursOver.

 

[lars7]

Hi Remou,
It's working great, I put in 9 hrs (1 day = 7hrs) for someone and the report shows Annual leave taken as 1.3 days and Annual leave left as 23.7 days from a total of 25, 14 hrs will result in Annual leave taken as 2 days and Annual leave left as 23 days. This is what I wanted.