Results 1 to 7 of 7

20050924, 16:16 #1
 Join Date
 Mar 2001
 Location
 Norwich, Connecticut, USA
 Posts
 159
 Thanks
 20
 Thanked 0 Times in 0 Posts
Need Help With Formula (Excel 2003 SP1)
I need help with a formula in the attached sample worksheet (scanned with NAV).
Columns A and B indicate the date and times of day (military time, text format) that I take a particular measurement. Column C converts column B time into a decimal ranging between 0.00 (midnight, begin day) and 1.00 (midnight, end day)

20050924, 17:58 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,332
 Thanks
 3
 Thanked 218 Times in 201 Posts
Re: Need Help With Formula (Excel 2003 SP1)
Hi,
How would a formula know that the data in row 10 is the last measurement for the day before rather than the first measurement for the day indicated? Is there a specific time range that should be regarded as belonging to the previous day? If not, I don't see how you could accomplish this with just the data given.Regards,
Rory
Microsoft MVP  Excel

20050924, 18:03 #3
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Need Help With Formula (Excel 2003 SP1)
If I understand the worksheet (which is doubtful <img src=/S/wink.gif border=0 alt=wink width=15 height=15>), the problem is not your formula, but rather your data. It appears that the date in column A is not the date of the time in column B, but rather the date that the shift began, so unless the person began work at 0100, the date in A10 should be 2 Sep 2005.
The formula in B8 is =IF(B8<>"",(MOD(B8,100)/1440+TRUNC(B8/100)/24),"") which says: If there is data (If(B8<>""), then take the minutes (MOD(B8,100)) and divide then by the number of munutes in a day (1440=24*60), then add to that the number of hours (TRUNC(B8/100)) divided by the number of hours in a day (24). For example, if B8 contained 1200, then the calculation would be MOD(1200,100)/1440+TRUNC(1200/100)/24 = 0/1440 + 12/24 = .5
HTH Sam<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20050924, 21:04 #4
 Join Date
 Sep 2005
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need Help With Formula (Excel 2003 SP1)
In E8
=IF(ROW()=MATCH(A8,A:A,0),"A",IF(ROW()=LARGE((A$8: A$1000=A8)*ROW(A$8:A$1000),1),"P","M"))
The above is an array formula, which requires entry via Control+Shift+Enter [CSE], not just Enter. Any direct edit of the formula will require reentry via CSE. You can tell if the formula has been entered correctly, as it will have braces  { }  around it afterward

20050924, 22:24 #5
 Join Date
 Mar 2001
 Location
 Norwich, Connecticut, USA
 Posts
 159
 Thanks
 20
 Thanked 0 Times in 0 Posts
Re: Need Help With Formula (Excel 2003 SP1)
Thanks for the replies. First of all, a correction. I need help with the formula for column D, not column C. Sorry for the mistype.
Rory, you identified the problem exactly. I

20050924, 22:40 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Need Help With Formula (Excel 2003 SP1)
In cell A14, the date is 5Sep05, although technically, the measurement was taken on the 6th, I suppose. Because the date is the same as in A13, the formula in D14 adds 1 to the time.
Similarly, if you want the formula in D10 to add 1, you should put 2Sep05 in cell A1, because you want the measurement to count with that date. As it is now, A10 is not consisten with A14, A20 and A24.
An alternative would be to always fill in the calendar date, i.e. 3Sep05 in A10, 6Sep05 in A14 etc., and specify up to what time you want the measurement to be counted as a measurement of the previous day, for example everything before 3:00 AM. The formula in D8 would then become
=C8+(C8<3/24)

20050925, 18:27 #7
 Join Date
 Mar 2001
 Location
 Norwich, Connecticut, USA
 Posts
 159
 Thanks
 20
 Thanked 0 Times in 0 Posts
Re: Need Help With Formula (Excel 2003 SP1)
Thanks for the reply, Hans. Works like a charm  your formula solved my problem!
Best regards.
Robert