Results 1 to 2 of 2
2006-05-08, 20:56 #1
- Join Date
- Oct 2005
- Thanked 1 Time in 1 Post
Calculate time elapsed (Excel 2003)
Hi folks...I need some help with a time sheet that I am making. I have attached a copy of it. It is fairly simple, but there are a couple of problems. Employees enter dates and times (absent) from work and choose an Absence Code...some of these only calculate in 1/2-day or full-day mode; others will calculate in actual time (ie: 2.333 hours) ...this is by design b/c some of the absences are only taken in 1/2-day or full-day manner (and others (APPT for medical) are taken in actual time (and then I round up or down). My first problem is:
(1) I have a formula that will count multiple days (eg: the 3-5) so that if an employee takes vacation (VAC) for those 3 days, it will total the amount of time taken (eg: 8:00-12:00 is a 1/2-day and if taken on the 3rd-5th, it equals 1.5 days of vacation....If I input 8:00-12:00 and use APPT for an absence code, it records 4 hours b/c Appointments are recorded in actual time....and in the 1/2-, who-day manner, I have allowed for a 1-hour lunch, so that being gone from 8-4:15PM (on Vacation) records as 7.250 hours. BUT...if I input time absent eg: 23:00 to 5:00 (11 pm on the 3rd to 5 AM on the 4th, straddling midnight) it produces a negative....how can I keep the existing formula (or have a better one) that will permit multiple days and still have the formula work if the 'work day' straddles midnight ?
(2) My answer to (1) could be in just entering a "From / Start" date...if I do that, and do not enter an "End / To" date, it works properly, other than when the start date is the 1st day of the month ?
Any suggestions would be very helpful.....ps: I also have a separate sheet that records Overtime....somehow, during the development of this sheet, I ended up with these formulae:
where N and O are the start & finsh times respectively.
I have also seen =MIN((O31-N31+(O31<N31))*24,7.25)......any ideas a to which of the 3 Overtime formulae would be best ??
2006-05-08, 21:05 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Calculate time elapsed (Excel 2003)
Reposted as <post:=573,070>post 573,070</post:>. This one is locked to avoid further duplication.