Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    <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>

  4. #4
    New Lounger
    Join Date
    Sep 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    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
  •