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

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

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

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

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