| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I WANT TO CREATE A FORMULA USING DATES
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 aU5, 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I WANT TO CREATE A FORMULA USING DATES
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 "armymatt" wrote: 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 aU5, 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I WANT TO CREATE A FORMULA USING DATES
hi
don't use this formula. i just did another test and it failed. working. regards FSt1 "FSt1" wrote: 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 "armymatt" wrote: 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 aU5, 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I WANT TO CREATE A FORMULA USING DATES
hi
use this one instead.... =IF(AND(ISNUMBER(U5)=TRUE,ISNUMBER(V5)=TRUE,ISNUMB ER(W5)=TRUE,Sheet2!H5="X"),TODAY(),"") regards FSt1 "FSt1" wrote: hi don't use this formula. i just did another test and it failed. working. regards FSt1 "FSt1" wrote: 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 "armymatt" wrote: 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 aU5, 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I WANT TO CREATE A FORMULA USING DATES
.... which can, of course, be simplified to
=IF(AND(ISNUMBER(U5),ISNUMBER(V5),ISNUMBER(W5),She et2!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. -- David Biddulph "FSt1" wrote in message ... hi use this one instead.... =IF(AND(ISNUMBER(U5)=TRUE,ISNUMBER(V5)=TRUE,ISNUMB ER(W5)=TRUE,Sheet2!H5="X"),TODAY(),"") regards FSt1 "FSt1" wrote: hi don't use this formula. i just did another test and it failed. working. regards FSt1 "FSt1" wrote: 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 "armymatt" wrote: 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 aU5, 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I WANT TO CREATE A FORMULA USING DATES
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! "FSt1" wrote: hi use this one instead.... =IF(AND(ISNUMBER(U5)=TRUE,ISNUMBER(V5)=TRUE,ISNUMB ER(W5)=TRUE,Sheet2!H5="X"),TODAY(),"") regards FSt1 "FSt1" wrote: hi don't use this formula. i just did another test and it failed. working. regards FSt1 "FSt1" wrote: 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 "armymatt" wrote: 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 aU5, 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I WANT TO CREATE A FORMULA USING DATES
David,
You're right I do want the date to freeze, what is a VBA solution? "David Biddulph" wrote: .... which can, of course, be simplified to =IF(AND(ISNUMBER(U5),ISNUMBER(V5),ISNUMBER(W5),She et2!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. -- David Biddulph "FSt1" wrote in message ... hi use this one instead.... =IF(AND(ISNUMBER(U5)=TRUE,ISNUMBER(V5)=TRUE,ISNUMB ER(W5)=TRUE,Sheet2!H5="X"),TODAY(),"") regards FSt1 "FSt1" wrote: hi don't use this formula. i just did another test and it failed. working. regards FSt1 "FSt1" wrote: 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 "armymatt" wrote: 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 aU5, 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I WANT TO CREATE A FORMULA USING DATES
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...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 -- David Biddulph "armymatt" wrote in message ... David, You're right I do want the date to freeze, what is a VBA solution? "David Biddulph" wrote: .... which can, of course, be simplified to =IF(AND(ISNUMBER(U5),ISNUMBER(V5),ISNUMBER(W5),She et2!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. -- David Biddulph "FSt1" wrote in message ... hi use this one instead.... =IF(AND(ISNUMBER(U5)=TRUE,ISNUMBER(V5)=TRUE,ISNUMB ER(W5)=TRUE,Sheet2!H5="X"),TODAY(),"") regards FSt1 "FSt1" wrote: hi don't use this formula. i just did another test and it failed. working. regards FSt1 "FSt1" wrote: 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 "armymatt" wrote: 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 aU5, 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I WANT TO CREATE A FORMULA USING DATES
thanks so much for the help
"David Biddulph" wrote: 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...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 -- David Biddulph "armymatt" wrote in message ... David, You're right I do want the date to freeze, what is a VBA solution? "David Biddulph" wrote: .... which can, of course, be simplified to =IF(AND(ISNUMBER(U5),ISNUMBER(V5),ISNUMBER(W5),She et2!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. -- David Biddulph "FSt1" wrote in message ... hi use this one instead.... =IF(AND(ISNUMBER(U5)=TRUE,ISNUMBER(V5)=TRUE,ISNUMB ER(W5)=TRUE,Sheet2!H5="X"),TODAY(),"") regards FSt1 "FSt1" wrote: hi don't use this formula. i just did another test and it failed. working. regards FSt1 "FSt1" wrote: 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 "armymatt" wrote: 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 aU5, 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create formula to calc difference in dates? | Excel Discussion (Misc queries) | |||
| Need to create a formula that counts dates | Excel Discussion (Misc queries) | |||
| HOW CREATE FORMULA TO ADD THE COLUMN CELLS THAT CONTAINING DATES | Excel Worksheet Functions | |||
| Formula to Create Consecutive #s for Non-Linear Dates | Excel Worksheet Functions | |||
| How can I create a formula to locate data between two dates? | Excel Worksheet Functions | |||
Linear Mode
