Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
duration days
hi all
i want to ask about this calculation: if i have a date for sell and the date for billing and i want to caculate the diffrence between them with a quirey if the days more than 10 days the result no discount and if it is less than 10 days it eill be extra discount like 3% and if the days more than 20 days that means tax 10% can any body help me? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
duration days, using datedif function
=IF(DATEDIF(A1,B1,"D")<=10,(C1*0.97),IF(DATEDIF(A1 ,B1,"D")20,(C1*1.1),0))
for this example place the start date in A1 Current Date in B1 Value to be calculated in C1 Copy and pste this formula in D1 Dave |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
duration days, using datedif function
hi curlydave,
i am sorry it doesnt work! something is wrong "curlydave" كتب: =IF(DATEDIF(A1,B1,"D")<=10,(C1*0.97),IF(DATEDIF(A1 ,B1,"D")20,(C1*1.1),0)) for this example place the start date in A1 Current Date in B1 Value to be calculated in C1 Copy and pste this formula in D1 Dave |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
duration days, using datedif function
well what's wrong??
jack nelson wrote: hi curlydave, i am sorry it doesnt work! something is wrong "curlydave" كتب: =IF(DATEDIF(A1,B1,"D")<=10,(C1*0.97),IF(DATEDIF(A1 ,B1,"D")20,(C1*1..1),0)) for this example place the start date in A1 Current Date in B1 Value to be calculated in C1 Copy and pste this formula in D1 Dave |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
duration days, using datedif function
Try this using the same cells that Curly referred to:
=IF(DATEDIF(A1,B1,"D")<=10,(C1*0.97),IF(DATEDIF(A1 ,B1,"D")<=20,(C1*1),IF(DATEDIF(A1,B1,"D")20,(C1*1 .1),0))) This one assumes that if the number of days is in between 10 and 20, then no change in price is applied which is what I think that Curly's formula was missing. Other than that, it worked fine for me! "jack nelson" wrote: hi curlydave, i am sorry it doesnt work! something is wrong "curlydave" كتب: =IF(DATEDIF(A1,B1,"D")<=10,(C1*0.97),IF(DATEDIF(A1 ,B1,"D")20,(C1*1.1),0)) for this example place the start date in A1 Current Date in B1 Value to be calculated in C1 Copy and pste this formula in D1 Dave |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
duration days, using datedif function
thanks alot
it is well done but i want to count how many days between this two date? "Tom" كتب: Try this using the same cells that Curly referred to: =IF(DATEDIF(A1,B1,"D")<=10,(C1*0.97),IF(DATEDIF(A1 ,B1,"D")<=20,(C1*1),IF(DATEDIF(A1,B1,"D")20,(C1*1 .1),0))) This one assumes that if the number of days is in between 10 and 20, then no change in price is applied which is what I think that Curly's formula was missing. Other than that, it worked fine for me! "jack nelson" wrote: hi curlydave, i am sorry it doesnt work! something is wrong "curlydave" كتب: =IF(DATEDIF(A1,B1,"D")<=10,(C1*0.97),IF(DATEDIF(A1 ,B1,"D")20,(C1*1.1),0)) for this example place the start date in A1 Current Date in B1 Value to be calculated in C1 Copy and pste this formula in D1 Dave |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
duration days
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
duration days, using datedif function
To count the number of days simply put into a cell:
=B1-A1 make sure that the cell you enter this formula into is formatted for GENERAL otherwise a date format will be applied. Tom "jack nelson" wrote: thanks alot it is well done but i want to count how many days between this two date? "Tom" كتب: Try this using the same cells that Curly referred to: =IF(DATEDIF(A1,B1,"D")<=10,(C1*0.97),IF(DATEDIF(A1 ,B1,"D")<=20,(C1*1),IF(DATEDIF(A1,B1,"D")20,(C1*1 .1),0))) This one assumes that if the number of days is in between 10 and 20, then no change in price is applied which is what I think that Curly's formula was missing. Other than that, it worked fine for me! "jack nelson" wrote: hi curlydave, i am sorry it doesnt work! something is wrong "curlydave" كتب: =IF(DATEDIF(A1,B1,"D")<=10,(C1*0.97),IF(DATEDIF(A1 ,B1,"D")20,(C1*1.1),0)) for this example place the start date in A1 Current Date in B1 Value to be calculated in C1 Copy and pste this formula in D1 Dave |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
duration days
On Sat, 26 Aug 2006 23:14:01 -0700, jack nelson
wrote: hi ron, i said if the A1 less than ten days the result will be the Poundage for the sales man. if it is more than ten days and less than twenty days no Poundage if the date more than twenty dates the sales man will get a Deduction that way help my to calculate the salary for the sales man Did the formula I provided do what you want? In your description you do not include what you want to happen if the difference is exactly equal to ten days or exactly equal to 20 days. You wrote three conditions: "less than ten days ... "more than ten days and less than twenty days ... "more than twenty dates ... So you have described what you want to happen if the value is "less than 10 days" or "more than ten days" but neither of those options include the value being "equal to ten days". I assumed when you wrote "less than ten days" that you meant "less than or equal to ten days", and the same with twenty days. If that is not what you want, you can alter the comparison operators appropriately in the formula I provided. "Ron Rosenfeld" ???: On Sat, 26 Aug 2006 03:29:01 -0700, jack nelson <jack wrote: hi all i want to ask about this calculation: if i have a date for sell and the date for billing and i want to caculate the diffrence between them with a quirey if the days more than 10 days the result no discount and if it is less than 10 days it eill be extra discount like 3% and if the days more than 20 days that means tax 10% can any body help me? A1: Sell Date B1: Bill Date C1: Cost And your specifications don't indicate what you want to happen if the days is exactly equal to ten days, so I made an assumption: =C1*IF((B1-A1)<=10,0.97,IF((B1-A1)<=20,1,1.1)) --ron --ron |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
duration days
hi ron,
thank you for your comment this is exactly what i mean and i will notice that in future. thanks again for every thing "Ron Rosenfeld" كتب: On Sat, 26 Aug 2006 23:14:01 -0700, jack nelson wrote: hi ron, i said if the A1 less than ten days the result will be the Poundage for the sales man. if it is more than ten days and less than twenty days no Poundage if the date more than twenty dates the sales man will get a Deduction that way help my to calculate the salary for the sales man Did the formula I provided do what you want? In your description you do not include what you want to happen if the difference is exactly equal to ten days or exactly equal to 20 days. You wrote three conditions: "less than ten days ... "more than ten days and less than twenty days ... "more than twenty dates ... So you have described what you want to happen if the value is "less than 10 days" or "more than ten days" but neither of those options include the value being "equal to ten days". I assumed when you wrote "less than ten days" that you meant "less than or equal to ten days", and the same with twenty days. If that is not what you want, you can alter the comparison operators appropriately in the formula I provided. "Ron Rosenfeld" ???: On Sat, 26 Aug 2006 03:29:01 -0700, jack nelson <jack wrote: hi all i want to ask about this calculation: if i have a date for sell and the date for billing and i want to caculate the diffrence between them with a quirey if the days more than 10 days the result no discount and if it is less than 10 days it eill be extra discount like 3% and if the days more than 20 days that means tax 10% can any body help me? A1: Sell Date B1: Bill Date C1: Cost And your specifications don't indicate what you want to happen if the days is exactly equal to ten days, so I made an assumption: =C1*IF((B1-A1)<=10,0.97,IF((B1-A1)<=20,1,1.1)) --ron --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAn any one solve this problem of days months and years.its urgent plzzzzzzzzzzzz | Excel Discussion (Misc queries) | |||
Please Help With Days Elapsed And Days Remaining Calculation | Excel Worksheet Functions | |||
No. of days split into periods | Excel Discussion (Misc queries) | |||
Calendar Days and Option Buttons | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions |