| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in "If" formula
Hi,
I am trying to setup an if statement to calculate the $ amount for all jobs approved but not yet started. i need to know how to write ex: "if A3 has a date then calculate the formula" into the "if statement". Column F= Date Approved Column G= Estimate Amount Column L = Invoice date Column H = Date Started So if there is a date in F20 and no date in L20 and no date H20 then 0 otherwise G20 So far i have =if(and(L20=" ",H20=" ",f20=DATE???),G20,0) I get lost at the date part...hopefully i'm on the right track.. *The reason i have included date started is because i have to setup a different formula for jobs that have been started and not yet approved. Any help would be appreciated. Thanks :) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date in "If" formula
Try
=IF(and(F20<"",L20="",H20=""),0,G20) "" has two " with nothing in between the pair "Saadia" wrote: Hi, I am trying to setup an if statement to calculate the $ amount for all jobs approved but not yet started. i need to know how to write ex: "if A3 has a date then calculate the formula" into the "if statement". Column F= Date Approved Column G= Estimate Amount Column L = Invoice date Column H = Date Started So if there is a date in F20 and no date in L20 and no date H20 then 0 otherwise G20 So far i have =if(and(L20=" ",H20=" ",f20=DATE???),G20,0) I get lost at the date part...hopefully i'm on the right track.. *The reason i have included date started is because i have to setup a different formula for jobs that have been started and not yet approved. Any help would be appreciated. Thanks :) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date in "If" formula
Hi,
Thanks for your reply. I tried it out, but doesn't seem to work for some reason. On jobs that are approved it's resulting in a zero and the remaining ones it's giving me the estimate amount. I double checked my cells and the order of the true and false values and it all looks correct. the date cells are formated as dates... Any ideas? "Sheeloo" wrote: Try =IF(and(F20<"",L20="",H20=""),0,G20) "" has two " with nothing in between the pair "Saadia" wrote: Hi, I am trying to setup an if statement to calculate the $ amount for all jobs approved but not yet started. i need to know how to write ex: "if A3 has a date then calculate the formula" into the "if statement". Column F= Date Approved Column G= Estimate Amount Column L = Invoice date Column H = Date Started So if there is a date in F20 and no date in L20 and no date H20 then 0 otherwise G20 So far i have =if(and(L20=" ",H20=" ",f20=DATE???),G20,0) I get lost at the date part...hopefully i'm on the right track.. *The reason i have included date started is because i have to setup a different formula for jobs that have been started and not yet approved. Any help would be appreciated. Thanks :) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date in "If" formula
Hi Saadia
Try =IF(and(F20<"",L20="",H20=""),G20,0) I think you made a mistake in your logic. You said So if there is a date in F20 and no date in L20 and no date H20 then 0 otherwise G20 but maybe you meant So if there is a date in F20 and no date in L20 and no date H20 then G20 otherwise 0 Just guessing, try it Regards John "Saadia" wrote in message ... Hi, Thanks for your reply. I tried it out, but doesn't seem to work for some reason. On jobs that are approved it's resulting in a zero and the remaining ones it's giving me the estimate amount. I double checked my cells and the order of the true and false values and it all looks correct. the date cells are formated as dates... Any ideas? "Sheeloo" wrote: Try =IF(and(F20<"",L20="",H20=""),0,G20) "" has two " with nothing in between the pair "Saadia" wrote: Hi, I am trying to setup an if statement to calculate the $ amount for all jobs approved but not yet started. i need to know how to write ex: "if A3 has a date then calculate the formula" into the "if statement". Column F= Date Approved Column G= Estimate Amount Column L = Invoice date Column H = Date Started So if there is a date in F20 and no date in L20 and no date H20 then 0 otherwise G20 So far i have =if(and(L20=" ",H20=" ",f20=DATE???),G20,0) I get lost at the date part...hopefully i'm on the right track.. *The reason i have included date started is because i have to setup a different formula for jobs that have been started and not yet approved. Any help would be appreciated. Thanks :) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date in "If" formula
Did you want the opposite?
=IF(and(F20<"",L20="",H20=""),G20,0) This will show the value in G20 if F20 is not bland AND L20 AND H20 are ... "Saadia" wrote: Hi, Thanks for your reply. I tried it out, but doesn't seem to work for some reason. On jobs that are approved it's resulting in a zero and the remaining ones it's giving me the estimate amount. I double checked my cells and the order of the true and false values and it all looks correct. the date cells are formated as dates... Any ideas? "Sheeloo" wrote: Try =IF(and(F20<"",L20="",H20=""),0,G20) "" has two " with nothing in between the pair "Saadia" wrote: Hi, I am trying to setup an if statement to calculate the $ amount for all jobs approved but not yet started. i need to know how to write ex: "if A3 has a date then calculate the formula" into the "if statement". Column F= Date Approved Column G= Estimate Amount Column L = Invoice date Column H = Date Started So if there is a date in F20 and no date in L20 and no date H20 then 0 otherwise G20 So far i have =if(and(L20=" ",H20=" ",f20=DATE???),G20,0) I get lost at the date part...hopefully i'm on the right track.. *The reason i have included date started is because i have to setup a different formula for jobs that have been started and not yet approved. Any help would be appreciated. Thanks :) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date in "If" formula
Ok that worked, however it is giving me estimate amounts for jobs
that have no date in the approved column but some writting ex..lost bid etc... Is there any way for it to solve only if there is a date and disregard anything else that is in that cell? Thanks for your help "Saadia" wrote: Hi, I am trying to setup an if statement to calculate the $ amount for all jobs approved but not yet started. i need to know how to write ex: "if A3 has a date then calculate the formula" into the "if statement". Column F= Date Approved Column G= Estimate Amount Column L = Invoice date Column H = Date Started So if there is a date in F20 and no date in L20 and no date H20 then 0 otherwise G20 So far i have =if(and(L20=" ",H20=" ",f20=DATE???),G20,0) I get lost at the date part...hopefully i'm on the right track.. *The reason i have included date started is because i have to setup a different formula for jobs that have been started and not yet approved. Any help would be appreciated. Thanks :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula to determine if multiple dates are "greater than" one date | Excel Worksheet Functions | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Linear Mode
