ExcelBanter - Date Time Field

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Date Time Field (https://www.excelbanter.com/excel-discussion-misc-queries/89422-date-time-field.html)

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

Thanks Dave O. That works perfectly

"Dave O" wrote:

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