# Thread: Need Help With Formula (Excel 2003 SP-1)

1. ## Need Help With Formula (Excel 2003 SP-1)

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)

2. ## Re: Need Help With Formula (Excel 2003 SP-1)

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.

3. ## Re: Need Help With Formula (Excel 2003 SP-1)

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

4. ## Re: Need Help With Formula (Excel 2003 SP-1)

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 re-entry via CSE. You can tell if the formula has been entered correctly, as it will have braces - { } - around it afterward

5. ## Re: Need Help With Formula (Excel 2003 SP-1)

Thanks for the replies. First of all, a correction. I need help with the formula for column D, not column C. Sorry for the mis-type.

Rory, you identified the problem exactly. I

6. ## Re: Need Help With Formula (Excel 2003 SP-1)

In cell A14, the date is 5-Sep-05, 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 2-Sep-05 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. 3-Sep-05 in A10, 6-Sep-05 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)

7. ## Re: Need Help With Formula (Excel 2003 SP-1)

Thanks for the reply, Hans. Works like a charm - your formula solved my problem!

Best regards.

Robert

#### Posting Permissions

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