# Thread: Calculate time elapsed #2 (Excel 2003)

1. ## 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. ## 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. ## Re: Calculate time elapsed #2 (Excel 2003)

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

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•