Results 1 to 14 of 14

20060508, 20:58 #1
 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/2day or fullday 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/2day or fullday 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 35) so that if an employee takes vacation (VAC) for those 3 days, it will total the amount of time taken (eg: 8:0012:00 is a 1/2day and if taken on the 3rd5th, it equals 1.5 days of vacation....If I input 8:0012: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, whoday manner, I have allowed for a 1hour lunch, so that being gone from 84: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((O31N31)*24,7.25))
and
=IF(N32>O32,CEILING(("24:00"N32+O32),7.25),MIN((O32N32)*24,7.25))
where N and O are the start & finsh times respectively.
I have also seen =MIN((O31N31+(O31<N31))*24,7.25)......any ideas a to which of the 3 Overtime formulae would be best ??

20060508, 21:04 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20060508, 21:08 #3
 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]

20060508, 21:23 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Calculate time elapsed #2 (Excel 2003)
Try changing E10D10 to E10D10+(E10<D10) in the formula in G10.
The formula =MIN((O31N31+(O31<N31))*24,7.25) is probably the most efficient.

20060508, 22:20 #5
 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/2day, fullday code such as VAC) it shows 7.583 hours....do I make your suggested substition for each occurence of E10D10 ??.....I even tried using E10D10+(e10<d10,1) but that didn't work.......

20060508, 22:25 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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 +(EndTimeStartTime)

20060508, 22:40 #7
 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 reattached the same sample with your proposed changes to show you if you wish...

20060508, 22:43 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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?

20060508, 23:25 #9
 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 1016 contain my original formula and you will see wrong times (or no times) in rows 15 & 16.
Rows 1725 (yellow) contain my original formula modified as per your suggestions (I hope)....rows 1518, row 21 and 2325 [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...

20060508, 23:42 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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...)

20060509, 00:26 #11
 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 34567; the time is 8:0016: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 83+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 reattached a sample...see pink rows

20060509, 04:50 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20060509, 21:43 #13
 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/2day (3.625 hrs) or fullday (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.

20060509, 22:05 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.