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

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

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

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" <(E-Mail Removed)> wrote in message
news:96621D31-20EB-4719-9BED-(E-Mail Removed)...
> 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

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

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