Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula question
Hi everyone and thanks in advance for any help you can offer. One of you guys
always seems to have the answer I need. I am using XP Pro and Office Pro 2003. I have built an invoice and column A is formatted for dates. The date of the activity is entered in column A, the activity description in column B and the amount of the charge in column C. The dates are normally not within the same month (i.e. 11/05/08, 01/15/09, 02/03/09.) The bottom cell in column A for the invoice is A35. I need a formula for A35 that will look at the column entries above A35, look at the last entry made (in this case 02/03/09) and then return an answer of 02/28/09. In other words I want the answer to be the last day of the month of the latest month entered above. Clear as mud?? :) Anyhow, if I've explained myself properly and you can offer some help I'd sure appreciate it. Thanks, Jim |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula question
A great question!
In A35 enter: =DATE(YEAR(LOOKUP(99^99,A1:A34)),MONTH(LOOKUP(99^9 9,A1:A34))+1,0) -- Gary''s Student - gsnu200842 "Jim Peterson" wrote: Hi everyone and thanks in advance for any help you can offer. One of you guys always seems to have the answer I need. I am using XP Pro and Office Pro 2003. I have built an invoice and column A is formatted for dates. The date of the activity is entered in column A, the activity description in column B and the amount of the charge in column C. The dates are normally not within the same month (i.e. 11/05/08, 01/15/09, 02/03/09.) The bottom cell in column A for the invoice is A35. I need a formula for A35 that will look at the column entries above A35, look at the last entry made (in this case 02/03/09) and then return an answer of 02/28/09. In other words I want the answer to be the last day of the month of the latest month entered above. Clear as mud?? :) Anyhow, if I've explained myself properly and you can offer some help I'd sure appreciate it. Thanks, Jim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula question
=EOMONTH(MAX(A1:A35),0) Adjust the date range in the MAX statement according to your needs. -- HTH JonR "Jim Peterson" wrote: Hi everyone and thanks in advance for any help you can offer. One of you guys always seems to have the answer I need. I am using XP Pro and Office Pro 2003. I have built an invoice and column A is formatted for dates. The date of the activity is entered in column A, the activity description in column B and the amount of the charge in column C. The dates are normally not within the same month (i.e. 11/05/08, 01/15/09, 02/03/09.) The bottom cell in column A for the invoice is A35. I need a formula for A35 that will look at the column entries above A35, look at the last entry made (in this case 02/03/09) and then return an answer of 02/28/09. In other words I want the answer to be the last day of the month of the latest month entered above. Clear as mud?? :) Anyhow, if I've explained myself properly and you can offer some help I'd sure appreciate it. Thanks, Jim |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula question
Jim,
=EOMONTH(MAX(A1:A34),0) this requires the analysis toolpak addin Tools|Addins check the analysis toolpak Mike "Jim Peterson" wrote: Hi everyone and thanks in advance for any help you can offer. One of you guys always seems to have the answer I need. I am using XP Pro and Office Pro 2003. I have built an invoice and column A is formatted for dates. The date of the activity is entered in column A, the activity description in column B and the amount of the charge in column C. The dates are normally not within the same month (i.e. 11/05/08, 01/15/09, 02/03/09.) The bottom cell in column A for the invoice is A35. I need a formula for A35 that will look at the column entries above A35, look at the last entry made (in this case 02/03/09) and then return an answer of 02/28/09. In other words I want the answer to be the last day of the month of the latest month entered above. Clear as mud?? :) Anyhow, if I've explained myself properly and you can offer some help I'd sure appreciate it. Thanks, Jim |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula question
Thanks to all three of you. I tried both answers and they both work great.
Thanks again for the help. Jim "Mike H" wrote: Jim, =EOMONTH(MAX(A1:A34),0) this requires the analysis toolpak addin Tools|Addins check the analysis toolpak Mike "Jim Peterson" wrote: Hi everyone and thanks in advance for any help you can offer. One of you guys always seems to have the answer I need. I am using XP Pro and Office Pro 2003. I have built an invoice and column A is formatted for dates. The date of the activity is entered in column A, the activity description in column B and the amount of the charge in column C. The dates are normally not within the same month (i.e. 11/05/08, 01/15/09, 02/03/09.) The bottom cell in column A for the invoice is A35. I need a formula for A35 that will look at the column entries above A35, look at the last entry made (in this case 02/03/09) and then return an answer of 02/28/09. In other words I want the answer to be the last day of the month of the latest month entered above. Clear as mud?? :) Anyhow, if I've explained myself properly and you can offer some help I'd sure appreciate it. Thanks, Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DUE DATE FORMULA QUESTION | Excel Worksheet Functions | |||
Date formula question | Excel Discussion (Misc queries) | |||
formula date question? | Excel Discussion (Misc queries) | |||
formula date question? | Excel Discussion (Misc queries) | |||
Formula date question | Excel Discussion (Misc queries) |