Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    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:

    =IF(N31>O31,MIN(("24:00"-N31+O31)*24,7.25),MIN((O31-N31)*24,7.25))

    and

    =IF(N32>O32,CEILING(("24:00"-N32+O32),7.25),MIN((O32-N32)*24,7.25))

    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 ??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculate time elapsed (Excel 2003)

    Reposted as <post:=573,070>post 573,070</post:>. This one is locked to avoid further duplication.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •