I WANT TO CREATE A FORMULA USING DATES

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

armymatt - 11 Feb 2008 17:50 GMT

I have dates in 3 seperate colums for 3 seperate tasks on Sheet 1. On Sheet 2
with in the same file i have a colomn that has either nothing or an X in it
when a specific task is complete.

the three date colomn's are:U5, V5, W5.
the other column is H5 on the other sheet.

I want to create a formula that when all 4 variables are entered, it puts
the date when all tasks were completed in cell T5 on sheet one.

what i have come up with so far is this
=IF(AND(U5=dd-mmm-yy,AND(V5=dd-mmm-yy,AND(W5=dd-mmm-yy,AND('sheet2'!H5=X)))),"TODAY(dd-mmm-yy)",""

any help would be greatly appreciated

Reply to this Message

FSt1 - 11 Feb 2008 18:31 GMT

hi,
try this....
=IF(AND(CELL("format",U5)="D4",CELL("format",V5)="D4",CELL("format",W5)="D4",Sheet2!H5="X"),TODAY(),"")

the format of =cell() may give you problems. per help on =Cell(), your
format = D1.
in a cell(off to the side) enter....=cell("format",U5). replace the return
format in the above formula with the format that =cell() returned.

regards
FSt1

> I have dates in 3 seperate colums for 3 seperate tasks on Sheet 1. On Sheet 2
> with in the same file i have a colomn that has either nothing or an X in it
[quoted text clipped - 10 lines]
>
> any help would be greatly appreciated

Reply to this Message

FSt1 - 11 Feb 2008 18:34 GMT

hi
don't use this formula. i just did another test and it failed.
working.

regards
FSt1

> hi,
> try this....
[quoted text clipped - 22 lines]
> >
> > any help would be greatly appreciated

Reply to this Message

FSt1 - 11 Feb 2008 18:43 GMT

hi
use this one instead....
=IF(AND(ISNUMBER(U5)=TRUE,ISNUMBER(V5)=TRUE,ISNUMBER(W5)=TRUE,Sheet2!H5="X"),TODAY(),"")

regards
FSt1

> hi
> don't use this formula. i just did another test and it failed.
[quoted text clipped - 29 lines]
> > >
> > > any help would be greatly appreciated

Reply to this Message

David Biddulph - 11 Feb 2008 19:00 GMT

... which can, of course, be simplified to
=IF(AND(ISNUMBER(U5),ISNUMBER(V5),ISNUMBER(W5),Sheet2!H5="X"),TODAY(),"")
as you don't need the =TRUE in each case.

I wonder, however, whether the OP really wants TODAY() as the result, as the
answer will then change tomorrow. If he wants the value to freeze, he
probably needs a VBA solution.

Signature

David Biddulph

> hi
> use this one instead....
[quoted text clipped - 40 lines]
>> > >
>> > > any help would be greatly appreciated

Reply to this Message

armymatt - 11 Feb 2008 19:14 GMT

David,

You're right I do want the date to freeze, what is a VBA solution?

> .... which can, of course, be simplified to
> =IF(AND(ISNUMBER(U5),ISNUMBER(V5),ISNUMBER(W5),Sheet2!H5="X"),TODAY(),"")
[quoted text clipped - 47 lines]
> >> > >
> >> > > any help would be greatly appreciated

Reply to this Message

David Biddulph - 12 Feb 2008 18:32 GMT

VBA is Visual Basic for Applications
To get an outline of what that means, type VBA into Excel Help
One of the pointers there is
http://office.microsoft.com/training/training.aspx?AssetID=RC011506201033

Looking at timestamps specifically, Google for Excel timestamp will find a
number of useful pages, such as
http://www.mcgimpsey.com/excel/timestamp.html

Signature

David Biddulph

> David,
>
[quoted text clipped - 57 lines]
>> >> > >
>> >> > > any help would be greatly appreciated

Reply to this Message

armymatt - 12 Feb 2008 19:02 GMT

thanks so much for the help

> VBA is Visual Basic for Applications
> To get an outline of what that means, type VBA into Excel Help
[quoted text clipped - 65 lines]
> >> >> > >
> >> >> > > any help would be greatly appreciated

Reply to this Message

armymatt - 11 Feb 2008 19:10 GMT

Hey I tried the first formula you said... and you're right I had problems but
I also did the "cell("format",U5)" in another cell and it came back with "G".
I plugged that into the formula as follows:

=IF(AND(CELL("format",U5)="G",CELL("format",V5)="G",CELL("format",W5)="G",'Warrior Task Tracker'!H5="X"),TODAY(),"")

and it worked as did the second formula you figured out. The only issue I
hope doesnt happen is that the date in the cell I put the formula doesnt
change, everytime I open the file. I want the date to reflect the day all
the other cells are filled.

Thanks for your help!

> hi
> use this one instead....
[quoted text clipped - 36 lines]
> > > >
> > > > any help would be greatly appreciated

Reply to this Message