Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Calculate time elapsed #2 (Excel 2003)

    IGNORE PREVIOUS POST B/C I FORGOT TO ATTACH FILE...SO HERE IS THE POST & THE FILE:
    ***************
    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 #2 (Excel 2003)

    For the future: you can edit an existing post, for example to attach a file you forgot - no need to duplicate the post.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculate time elapsed #2 (Excel 2003)

    ..thanks for that pointer Hans...[img]/forums/images/smilies/smile.gif[/img]

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

    Re: Calculate time elapsed #2 (Excel 2003)

    Try changing E10-D10 to E10-D10+(E10<D10) in the formula in G10.

    The formula =MIN((O31-N31+(O31<N31))*24,7.25) is probably the most efficient.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculate time elapsed #2 (Excel 2003)

    Hmm...thank you, altho when I try it, I get odd results...eg: if I record time starting on the 1st @ 23:00 and ending at 7:00 on the 2nd....and use TLT [one of the 'actual time' codes, rather than a 1/2-day, full-day code such as VAC) it shows 7.583 hours....do I make your suggested substition for each occurence of E10-D10 ??.....I even tried using E10-D10+(e10<d10,1) but that didn't work.......

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

    Re: Calculate time elapsed #2 (Excel 2003)

    Wherever you subtract two times and want to take into account that the end time might be on the next day, you must add +(EndTime-StartTime)

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculate time elapsed #2 (Excel 2003)

    ???...thanks for trying to help me figure this out, Hans, but it's not working.......I can re-attached the same sample with your proposed changes to show you if you wish...

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

    Re: Calculate time elapsed #2 (Excel 2003)

    Please do so, and could you also enter some dates/times for which the formulas return incorrect results?

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculate time elapsed #2 (Excel 2003)

    Hello again Hans....I have done up the sample (calling it ----29.2)......in column A, I have indicated what I am trying to record. Rows 10-16 contain my original formula and you will see wrong times (or no times) in rows 15 & 16.

    Rows 17-25 (yellow) contain my original formula modified as per your suggestions (I hope)....rows 15-18, row 21 and 23-25 [blocked in red] contain wrong times in column G (actual times) which leads to wrong times in column H when the times are rounded up (see formula in column H for rounding up, or down)......I seem to be getting worse at this as times passes...

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

    Re: Calculate time elapsed #2 (Excel 2003)

    You didn't follow my suggestion. See the attached - does that work better? I also removed the SUM function from the formulas, it didn't serve any purpose. I moved the 2nd date for rows that straddle midnight one day back, otherwise you'd count an extra day.

    (What was the purpose of all those empty code modules? I removed them - no point in getting a macro warning if you don't have macros...)

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculate time elapsed #2 (Excel 2003)

    ....than you very much fro your help, Hans.....as I study it some more, I wonder if it's possible to add something to the formula that will permit it to calculate multiple days of Vacation for a person whose work day straddles midnite...for example....someone who works 'day shift' puts in 5 days Vacation from the 3-4-5-6-7; the time is 8:00-16:15; it will total that as 5 * 7.250 = 36.25 hours.....[see pink row]....if someone else puts in for 5 days, but their shift straddles midnite, it looks like this -- 3rd/4th - 4th/5th - 5th/6th - 6th/7th - 7th/8th...it is still 5 days [see pink row] but it shows in the From column (A) as 3 and the To column ([img]/forums/images/smilies/cool.gif[/img] as 8...which works out as 8-3+1 * [the hours] = 43.5...even tho it really is only 5 days...??....am I explaining it properly ??...is there an adjustment to the formula that will compensate for this and still leave everything else as is ??...I have re-attached a sample...see pink rows

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

    Re: Calculate time elapsed #2 (Excel 2003)

    Enter 7 instead of 8 in column B. That way, the date range includes 5 days (3, 4, 5, 6, 7)
    If you prefer to enter 8, adjust the day calculation as in the attached workbook.

  13. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculate time elapsed #2 (Excel 2003)

    Good afternoon again, Hans....thank you very much for the attachment and advice...works like a charm......may I ask a couple of things --- if I decide that don't want to bother inputting a "TO" date in column B (other than if I wish to show a date range..eg: 1st to 5th), can you suggest an adjustment to the formula in column G that will work without a date in column B, but will also permit a shift to straddle midnite (eg: rows 15 & 16)...and will produce the proper result for items such as VAC (vacation) that calculate in 1/2-day (3.625 hrs) or full-day (7.250 hrs) or multiples thereof ??......and if you look at rows 19 & 24, there are different results......?..??.........I have attached the last sample that you sent me (with a slightly different file name), b/c as I studied the sample, it occured to me that I really don't require a TO date in column B if that date is the same as in column A......ps: sorry to be wandering a bit with this, and I do appreciate your assistance...thanks.

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

    Re: Calculate time elapsed #2 (Excel 2003)

    See attached version. In row 24, you MUST now enter 8 in the TO column, since the vacation ends on the 8th.

Posting Permissions

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