|
extract date from last cell
Hi, i have this code:
=IF(Acte!A1="","",IF(TODAY()=Acte!A1,"expired",Ac te!A1-TODAY())) This code extract a date from A1, but now i have a group of 4 cells with diferrent dates. A B C D 01.01.2009 01.03.2009 01.06.2009 01.09.2009 All i need is that the code look in "A1:D1" and if a cell it's bigger than today, than to take the data from the next cell. Ex: today is 30.06.2009, so the code to look in "D1" - 01.09.2009. Can this be done? Thanks. |
extract date from last cell
The below formula will return the first date which is more than today().
Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))) ) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have this code: =IF(Acte!A1="","",IF(TODAY()=Acte!A1,"expired",Ac te!A1-TODAY())) This code extract a date from A1, but now i have a group of 4 cells with diferrent dates. A B C D 01.01.2009 01.03.2009 01.06.2009 01.09.2009 All i need is that the code look in "A1:D1" and if a cell it's bigger than today, than to take the data from the next cell. Ex: today is 30.06.2009, so the code to look in "D1" - 01.09.2009. Can this be done? Thanks. |
extract date from last cell
Your formula is good, but there is any way to combine this two formulas?
Because i need the formula to calculate how days remains. Your formula minus today = 63 days. And i need this in one formula. =IF(Acte!A1="","",IF(TODAY()=Acte!A1,"expired",Ac te!A1-TODAY())) =INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))) ) Thanks allot! "Jacob Skaria" a scris: The below formula will return the first date which is more than today(). Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))) ) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have this code: =IF(Acte!A1="","",IF(TODAY()=Acte!A1,"expired",Ac te!A1-TODAY())) This code extract a date from A1, but now i have a group of 4 cells with diferrent dates. A B C D 01.01.2009 01.03.2009 01.06.2009 01.09.2009 All i need is that the code look in "A1:D1" and if a cell it's bigger than today, than to take the data from the next cell. Ex: today is 30.06.2009, so the code to look in "D1" - 01.09.2009. Can this be done? Thanks. |
extract date from last cell
Try the below. Again Array entered.
=IF(COUNT(A1:D1),IF(TODAY()=MAX(A1:D1),"Expired", INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))))-TODAY()),"") If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Your formula is good, but there is any way to combine this two formulas? Because i need the formula to calculate how days remains. Your formula minus today = 63 days. And i need this in one formula. =IF(Acte!A1="","",IF(TODAY()=Acte!A1,"expired",Ac te!A1-TODAY())) =INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))) ) Thanks allot! "Jacob Skaria" a scris: The below formula will return the first date which is more than today(). Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))) ) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have this code: =IF(Acte!A1="","",IF(TODAY()=Acte!A1,"expired",Ac te!A1-TODAY())) This code extract a date from A1, but now i have a group of 4 cells with diferrent dates. A B C D 01.01.2009 01.03.2009 01.06.2009 01.09.2009 All i need is that the code look in "A1:D1" and if a cell it's bigger than today, than to take the data from the next cell. Ex: today is 30.06.2009, so the code to look in "D1" - 01.09.2009. Can this be done? Thanks. |
extract date from last cell
WORKS GREAT!
Thanks!! "Jacob Skaria" a scris: Try the below. Again Array entered. =IF(COUNT(A1:D1),IF(TODAY()=MAX(A1:D1),"Expired", INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))))-TODAY()),"") If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Your formula is good, but there is any way to combine this two formulas? Because i need the formula to calculate how days remains. Your formula minus today = 63 days. And i need this in one formula. =IF(Acte!A1="","",IF(TODAY()=Acte!A1,"expired",Ac te!A1-TODAY())) =INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))) ) Thanks allot! "Jacob Skaria" a scris: The below formula will return the first date which is more than today(). Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))) ) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have this code: =IF(Acte!A1="","",IF(TODAY()=Acte!A1,"expired",Ac te!A1-TODAY())) This code extract a date from A1, but now i have a group of 4 cells with diferrent dates. A B C D 01.01.2009 01.03.2009 01.06.2009 01.09.2009 All i need is that the code look in "A1:D1" and if a cell it's bigger than today, than to take the data from the next cell. Ex: today is 30.06.2009, so the code to look in "D1" - 01.09.2009. Can this be done? Thanks. |
extract date from last cell
Hi Jacob, i know this is an old post, but i need to transform this code a
little bit. I need this code to do exactly the same thing, but only if in sheet 2, the same range, in corresponding cell, i enter bill's number/date. If the date has past and in sheet 2 corresponding cell is empty, then to display "Please Pay". If in corresponding cell is something, then the code to look in next cell(To do exactly was the code is doing now). Can this be done? Thanks! "Jacob Skaria" wrote: Try the below. Again Array entered. =IF(COUNT(A1:D1),IF(TODAY()=MAX(A1:D1),"Expired", INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))))-TODAY()),"") If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Your formula is good, but there is any way to combine this two formulas? Because i need the formula to calculate how days remains. Your formula minus today = 63 days. And i need this in one formula. =IF(Acte!A1="","",IF(TODAY()=Acte!A1,"expired",Ac te!A1-TODAY())) =INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))) ) Thanks allot! "Jacob Skaria" a scris: The below formula will return the first date which is more than today(). Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(A1:D1,,MIN(IF(A1:D1TODAY(),COLUMN(A1:D1))) ) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have this code: =IF(Acte!A1="","",IF(TODAY()=Acte!A1,"expired",Ac te!A1-TODAY())) This code extract a date from A1, but now i have a group of 4 cells with diferrent dates. A B C D 01.01.2009 01.03.2009 01.06.2009 01.09.2009 All i need is that the code look in "A1:D1" and if a cell it's bigger than today, than to take the data from the next cell. Ex: today is 30.06.2009, so the code to look in "D1" - 01.09.2009. Can this be done? Thanks. |
| All times are GMT +1. The time now is 10:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com