Hello!

I need to write a formula that will calculate how many days are left in a
specific month and can't seem to get a working one. Seems like it should be
relatively easy.

Example:

Cell A1 contains date of 03/09/98.
I need cell B1 to contain the resulting number as 22 days remaining.

Cell A2 contains date of 04/03/98
Cell B2 should contain the resulting number as 27 days remaining.

I tried this:
=(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-(DAY(A2)))
Which actually works, but I was just wondering if there was a less
complicated way? Trying to explain this one to the person requesting the
spreadsheet may not be the easiest of jobs and I know I'll get questioned on
it.

Any help would be appreciated!
Cheers,
Elf

Hi Elf,

Slightly less complicated:

=DAY(EOMONTH(A1,0))-DAY(A1)

This needs Analysis Toolpak to be installed. If you get a #NAME error:

ToolsAdd-ins, check Analysis Toolpak

--
Kind regards,

Niek Otten

"elfmajesty" wrote in message ...
Hello!

I need to write a formula that will calculate how many days are left in a
specific month and can't seem to get a working one. Seems like it should be
relatively easy.

Example:

Cell A1 contains date of 03/09/98.
I need cell B1 to contain the resulting number as 22 days remaining.

Cell A2 contains date of 04/03/98
Cell B2 should contain the resulting number as 27 days remaining.

I tried this:
=(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-(DAY(A2)))
Which actually works, but I was just wondering if there was a less
complicated way? Trying to explain this one to the person requesting the
spreadsheet may not be the easiest of jobs and I know I'll get questioned on
it.

Any help would be appreciated!
Cheers,
Elf

Of course, much simpler:

=EOMONTH(A1,0)-A1

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message ...
Hi Elf,

Slightly less complicated:

=DAY(EOMONTH(A1,0))-DAY(A1)

This needs Analysis Toolpak to be installed. If you get a #NAME error:

ToolsAdd-ins, check Analysis Toolpak

--
Kind regards,

Niek Otten

"elfmajesty" wrote in message ...
Hello!

I need to write a formula that will calculate how many days are left in a
specific month and can't seem to get a working one. Seems like it should be
relatively easy.

Example:

Cell A1 contains date of 03/09/98.
I need cell B1 to contain the resulting number as 22 days remaining.

Cell A2 contains date of 04/03/98
Cell B2 should contain the resulting number as 27 days remaining.

I tried this:
=(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-(DAY(A2)))
Which actually works, but I was just wondering if there was a less
complicated way? Trying to explain this one to the person requesting the
spreadsheet may not be the easiest of jobs and I know I'll get questioned on
it.

Any help would be appreciated!
Cheers,
Elf

Hi Elf,

=A1/24/60/60

Format Custom as d:hh:mm:ss

--
Kind regards,

Niek Otten

"elfmajesty" wrote in message ...
Hello!

I need to write a formula that will calculate how many days are left in a
specific month and can't seem to get a working one. Seems like it should be
relatively easy.

Example:

Cell A1 contains date of 03/09/98.
I need cell B1 to contain the resulting number as 22 days remaining.

Cell A2 contains date of 04/03/98
Cell B2 should contain the resulting number as 27 days remaining.

I tried this:
=(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-(DAY(A2)))
Which actually works, but I was just wondering if there was a less
complicated way? Trying to explain this one to the person requesting the
spreadsheet may not be the easiest of jobs and I know I'll get questioned on
it.

Any help would be appreciated!
Cheers,
Elf

Sorry about this one

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message ...
Hi Elf,

=A1/24/60/60

Format Custom as d:hh:mm:ss

--
Kind regards,

Niek Otten

"elfmajesty" wrote in message ...
Hello!

I need to write a formula that will calculate how many days are left in a
specific month and can't seem to get a working one. Seems like it should be
relatively easy.

Example:

Cell A1 contains date of 03/09/98.
I need cell B1 to contain the resulting number as 22 days remaining.

Cell A2 contains date of 04/03/98
Cell B2 should contain the resulting number as 27 days remaining.

I tried this:
=(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-(DAY(A2)))
Which actually works, but I was just wondering if there was a less
complicated way? Trying to explain this one to the person requesting the
spreadsheet may not be the easiest of jobs and I know I'll get questioned on
it.

Any help would be appreciated!
Cheers,
Elf

Elf,

You can use EOMONTH. You need to have the Analysis ToolPak. To
install go to ToolsAddins. Select the Analysis ToolPak check box.
Then you can apply this formula in A2.

=EOMONTH(A1,0)-A1

Format A2 as General. You can then copy this down as needed.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=524961

On Tue, 21 Mar 2006 11:21:27 -0800, elfmajesty
wrote:

Hello!

I need to write a formula that will calculate how many days are left in a
specific month and can't seem to get a working one. Seems like it should be
relatively easy.

Example:

Cell A1 contains date of 03/09/98.
I need cell B1 to contain the resulting number as 22 days remaining.

Cell A2 contains date of 04/03/98
Cell B2 should contain the resulting number as 27 days remaining.

I tried this:
=(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-(DAY(A2)))
Which actually works, but I was just wondering if there was a less
complicated way? Trying to explain this one to the person requesting the
spreadsheet may not be the easiest of jobs and I know I'll get questioned on
it.

Any help would be appreciated!
Cheers,
Elf

If you don't want to deal with the Analysis Tool Pak, you could use the
formula:

=32-DAY(A1)-DAY(A1-DAY(A1)+32)

Format as General or Number


--ron

=32-DAY(A1)-DAY(A1-DAY(A1)+32)

=(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-(DAY(A2)))
Which actually works, but I was just wondering if there was a less
complicated way? Trying to explain this one to the person requesting the
spreadsheet may not be the easiest of jobs and I know I'll get questioned
on
it.

If Elf thought that he was going to have trouble explaining it
before............

<g

--

Sandy

with @tiscali.co.uk

On Tue, 21 Mar 2006 21:07:27 -0000, "Sandy Mann"
wrote:

=32-DAY(A1)-DAY(A1-DAY(A1)+32)

=(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-(DAY(A2)))
Which actually works, but I was just wondering if there was a less
complicated way? Trying to explain this one to the person requesting the
spreadsheet may not be the easiest of jobs and I know I'll get questioned
on
it.

If Elf thought that he was going to have trouble explaining it
before............

<g

Explanations? Hmmph.

Well, with any date:

A1-DAY(A1) will always give the last day of the preceding month.

Add 32 to get into the following month.

32+ A1-Day(A1)

Then subtract the DAY of that date to get the last date of the current month.

32+A1-DAY(A1) - DAY(32+A1-DAY(A1))

From that subtract the current date in A1:

-A1 + 32+A1-DAY(A1) - DAY(32+A1-DAY(A1))

Remove the values that cancel out (-A1+A1)

32-DAY(A1) - DAY(32+A1-DAY(A1))


--ron

Nice explanation Ron, I especially like the:

Remove the values that cancel out (-A1+A1)

part. Lets hope that it satisfies Elf's colleague

--
Regards

Sandy

with @tiscali.co.uk


"Ron Rosenfeld" wrote in message
...
On Tue, 21 Mar 2006 21:07:27 -0000, "Sandy Mann"

wrote:

=32-DAY(A1)-DAY(A1-DAY(A1)+32)

=(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-(DAY(A2)))
Which actually works, but I was just wondering if there was a less
complicated way? Trying to explain this one to the person requesting
the
spreadsheet may not be the easiest of jobs and I know I'll get
questioned
on
it.

If Elf thought that he was going to have trouble explaining it
before............

<g

Explanations? Hmmph.

Well, with any date:

A1-DAY(A1) will always give the last day of the preceding month.

Add 32 to get into the following month.

32+ A1-Day(A1)

Then subtract the DAY of that date to get the last date of the current
month.

32+A1-DAY(A1) - DAY(32+A1-DAY(A1))

From that subtract the current date in A1:

-A1 + 32+A1-DAY(A1) - DAY(32+A1-DAY(A1))

Remove the values that cancel out (-A1+A1)

32-DAY(A1) - DAY(32+A1-DAY(A1))


--ron

If you don't want if install the Analysis Toolpak then slightly shorter and
still using your formula:

=DATE(YEAR(A2),MONTH(A2)+1,0)-A2

if you format the cell as General there is no need for the DAY() functions.
--
HTH

Sandy

with @tiscali.co.uk


"elfmajesty" wrote in message
...
Hello!

I need to write a formula that will calculate how many days are left in a
specific month and can't seem to get a working one. Seems like it should
be
relatively easy.

Example:

Cell A1 contains date of 03/09/98.
I need cell B1 to contain the resulting number as 22 days remaining.

Cell A2 contains date of 04/03/98
Cell B2 should contain the resulting number as 27 days remaining.

I tried this:
=(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-(DAY(A2)))
Which actually works, but I was just wondering if there was a less
complicated way? Trying to explain this one to the person requesting the
spreadsheet may not be the easiest of jobs and I know I'll get questioned
on
it.

Any help would be appreciated!
Cheers,
Elf