Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Field
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Field
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))))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Field
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))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing a text field to a date field | New Users to Excel | |||
Need help: convert seconds to date and time | Excel Discussion (Misc queries) | |||
How to get pivot table Time field to appear correctly | Excel Worksheet Functions | |||
How to Join/concatenate a date field with a time field in Excel? | Excel Discussion (Misc queries) | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |