Date Time Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to make the cost column do the following with a formula?

Start Cost
4/1/06 6:45 AM $10
4/1/06 7:00 AM $8
4/1/06 7:15 AM $6
4/1/06 6:00 PM $10
4/1/06 6:15 PM $8
4/1/06 6:30 PM $6
4/1/06 6:45 PM $6
4/1/06 7:00 PM $6
4/1/06 7:15 PM $6
4/2/06 6:45 AM $10
4/2/06 7:00 AM $8
4/2/06 7:15 AM $6
4/2/06 6:00 PM $10
4/2/06 6:15 PM $8
4/2/06 6:30 PM $6
4/2/06 6:45 PM $6
4/2/06 7:00 PM $6
4/2/06 7:15 PM $6
4/3/06 6:45 AM $10
4/3/06 7:00 AM $8
4/3/06 7:15 AM $6
4/3/06 7:30 AM $6
4/3/06 7:45 AM $6
4/3/06 6:00 PM $10
4/3/06 6:15 PM $8
4/3/06 6:30 PM $6
4/3/06 6:45 PM $6
4/3/06 7:00 PM $6
4/3/06 7:15 PM $6
4/3/06 7:30 PM $6

Basically I need to assign $10 cost to the first occurence in the AM and $10
cost to the first occurence in the PM. Then, I have to assign $8 cost to the
second occurence in the AM and $8 cost to the second occurence in the PM.
Then, I have to assign $6 cost to every occurence 3rd, 4th, or so on.
 
With headers in A1 and B1, a blank row 2, and Excel readable dates
starting in A3, I used this formula to generate the same results as
your sample data:
=IF(INT(A3)<>INT(A2),10,IF(AND(MOD(A3,1)>=0.5,MOD(A2,1)<0.5),10,IF(B2=10,8,IF(B2=8,6,IF(B2=6,6)))))
 
Back
Top