| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
formula help
Hi
I'm working on a sheet with 3 columns A1 B1 C1 D1 =Date ran =use buy = blank or =days remaining no work from todays date to use buy date example: In A1 is date ran 1/12/08, B1 needs to be 1 year from date ran, 1/12/09. i'm using =DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) in B1 and it seems to work. no onto C1, this is the one i can't figure out, if C1 is blank B1 needs to show date, if B1 contains the words no work, then i need B1 and D1 to be blank, in D1 i'm using this formula (thanks to the search fuction) =DATEDIF(TODAY(),MAX(A14,DATE(YEAR(TODAY())+(DATE( YEAR(TODAY()),MONTH(B14),DAY(A14))TODAY())+1,MONT H(A14),DAY(A14))),"d") and it also seems to work. thanks for any help you can offer. |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula help
In B14:
=IF(C14="No work","",DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) Note that by making B possibly blank, you'll need to account for that in column D In D14 =IF(B14="","What do you want to happen?",B1-TODAY()) And format cell as number. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Bowmanator" wrote: Hi I'm working on a sheet with 3 columns A1 B1 C1 D1 =Date ran =use buy = blank or =days remaining no work from todays date to use buy date example: In A1 is date ran 1/12/08, B1 needs to be 1 year from date ran, 1/12/09. i'm using =DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) in B1 and it seems to work. no onto C1, this is the one i can't figure out, if C1 is blank B1 needs to show date, if B1 contains the words no work, then i need B1 and D1 to be blank, in D1 i'm using this formula (thanks to the search fuction) =DATEDIF(TODAY(),MAX(A14,DATE(YEAR(TODAY())+(DATE( YEAR(TODAY()),MONTH(B14),DAY(A14))TODAY())+1,MONT H(A14),DAY(A14))),"d") and it also seems to work. thanks for any help you can offer. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula help
thanks, that worked great, once i figured out what you meant by "what you
want to happen", so i just deleted the wording and left the quotes now it shows a blank cell when it needs to. thanks again. "Luke M" wrote: In B14: =IF(C14="No work","",DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) Note that by making B possibly blank, you'll need to account for that in column D In D14 =IF(B14="","What do you want to happen?",B1-TODAY()) And format cell as number. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Bowmanator" wrote: Hi I'm working on a sheet with 3 columns A1 B1 C1 D1 =Date ran =use buy = blank or =days remaining no work from todays date to use buy date example: In A1 is date ran 1/12/08, B1 needs to be 1 year from date ran, 1/12/09. i'm using =DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) in B1 and it seems to work. no onto C1, this is the one i can't figure out, if C1 is blank B1 needs to show date, if B1 contains the words no work, then i need B1 and D1 to be blank, in D1 i'm using this formula (thanks to the search fuction) =DATEDIF(TODAY(),MAX(A14,DATE(YEAR(TODAY())+(DATE( YEAR(TODAY()),MONTH(B14),DAY(A14))TODAY())+1,MONT H(A14),DAY(A14))),"d") and it also seems to work. thanks for any help you can offer. |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula help
need help on one more thing.
after copy and pasting down in multible cells if day ran cell is blank B1and D1 shows data. how can i get those to be blank unless there's data in A1? "Luke M" wrote: In B14: =IF(C14="No work","",DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) Note that by making B possibly blank, you'll need to account for that in column D In D14 =IF(B14="","What do you want to happen?",B1-TODAY()) And format cell as number. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Bowmanator" wrote: Hi I'm working on a sheet with 3 columns A1 B1 C1 D1 =Date ran =use buy = blank or =days remaining no work from todays date to use buy date example: In A1 is date ran 1/12/08, B1 needs to be 1 year from date ran, 1/12/09. i'm using =DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) in B1 and it seems to work. no onto C1, this is the one i can't figure out, if C1 is blank B1 needs to show date, if B1 contains the words no work, then i need B1 and D1 to be blank, in D1 i'm using this formula (thanks to the search fuction) =DATEDIF(TODAY(),MAX(A14,DATE(YEAR(TODAY())+(DATE( YEAR(TODAY()),MONTH(B14),DAY(A14))TODAY())+1,MONT H(A14),DAY(A14))),"d") and it also seems to work. thanks for any help you can offer. |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula help
Change the formula in B14 to
=IF(OR(ISBLANK(A14),C14="No work"),"",DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Bowmanator" wrote: need help on one more thing. after copy and pasting down in multible cells if day ran cell is blank B1and D1 shows data. how can i get those to be blank unless there's data in A1? "Luke M" wrote: In B14: =IF(C14="No work","",DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) Note that by making B possibly blank, you'll need to account for that in column D In D14 =IF(B14="","What do you want to happen?",B1-TODAY()) And format cell as number. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Bowmanator" wrote: Hi I'm working on a sheet with 3 columns A1 B1 C1 D1 =Date ran =use buy = blank or =days remaining no work from todays date to use buy date example: In A1 is date ran 1/12/08, B1 needs to be 1 year from date ran, 1/12/09. i'm using =DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) in B1 and it seems to work. no onto C1, this is the one i can't figure out, if C1 is blank B1 needs to show date, if B1 contains the words no work, then i need B1 and D1 to be blank, in D1 i'm using this formula (thanks to the search fuction) =DATEDIF(TODAY(),MAX(A14,DATE(YEAR(TODAY())+(DATE( YEAR(TODAY()),MONTH(B14),DAY(A14))TODAY())+1,MONT H(A14),DAY(A14))),"d") and it also seems to work. thanks for any help you can offer. |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula help
That worked great, many thanks
"Luke M" wrote: Change the formula in B14 to =IF(OR(ISBLANK(A14),C14="No work"),"",DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Bowmanator" wrote: need help on one more thing. after copy and pasting down in multible cells if day ran cell is blank B1and D1 shows data. how can i get those to be blank unless there's data in A1? "Luke M" wrote: In B14: =IF(C14="No work","",DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) Note that by making B possibly blank, you'll need to account for that in column D In D14 =IF(B14="","What do you want to happen?",B1-TODAY()) And format cell as number. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Bowmanator" wrote: Hi I'm working on a sheet with 3 columns A1 B1 C1 D1 =Date ran =use buy = blank or =days remaining no work from todays date to use buy date example: In A1 is date ran 1/12/08, B1 needs to be 1 year from date ran, 1/12/09. i'm using =DATE(YEAR(A14)+1,MONTH(A14),DAY(A14)) in B1 and it seems to work. no onto C1, this is the one i can't figure out, if C1 is blank B1 needs to show date, if B1 contains the words no work, then i need B1 and D1 to be blank, in D1 i'm using this formula (thanks to the search fuction) =DATEDIF(TODAY(),MAX(A14,DATE(YEAR(TODAY())+(DATE( YEAR(TODAY()),MONTH(B14),DAY(A14))TODAY())+1,MONT H(A14),DAY(A14))),"d") and it also seems to work. thanks for any help you can offer. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
