software

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:

Toolsgt;Add-ins, check Analysis Toolpak

--
Kind regards,

Niek Otten

quot;elfmajestyquot; gt; wrote in message ...
gt; Hello!
gt;
gt; I need to write a formula that will calculate how many days are left in a
gt; specific month and can't seem to get a working one. Seems like it should be
gt; relatively easy.
gt;
gt; Example:
gt;
gt; Cell A1 contains date of 03/09/98.
gt; I need cell B1 to contain the resulting number as 22 days remaining.
gt;
gt; Cell A2 contains date of 04/03/98
gt; Cell B2 should contain the resulting number as 27 days remaining.
gt;
gt; I tried this:
gt; =(DAY(DATE(YEAR(A2),MONTH(A2) 1,0))-(DAY(A2)))
gt; Which actually works, but I was just wondering if there was a less
gt; complicated way? Trying to explain this one to the person requesting the
gt; spreadsheet may not be the easiest of jobs and I know I'll get questioned on
gt; it.
gt;
gt; Any help would be appreciated!
gt; Cheers,
gt; Elf
Hi Elf,

=A1/24/60/60

Format Custom as d:hh:mm:ss

--
Kind regards,

Niek Otten

quot;elfmajestyquot; gt; wrote in message ...
gt; Hello!
gt;
gt; I need to write a formula that will calculate how many days are left in a
gt; specific month and can't seem to get a working one. Seems like it should be
gt; relatively easy.
gt;
gt; Example:
gt;
gt; Cell A1 contains date of 03/09/98.
gt; I need cell B1 to contain the resulting number as 22 days remaining.
gt;
gt; Cell A2 contains date of 04/03/98
gt; Cell B2 should contain the resulting number as 27 days remaining.
gt;
gt; I tried this:
gt; =(DAY(DATE(YEAR(A2),MONTH(A2) 1,0))-(DAY(A2)))
gt; Which actually works, but I was just wondering if there was a less
gt; complicated way? Trying to explain this one to the person requesting the
gt; spreadsheet may not be the easiest of jobs and I know I'll get questioned on
gt; it.
gt;
gt; Any help would be appreciated!
gt; Cheers,
gt; Elf

Elf,

You can use EOMONTH. You need to have the Analysis ToolPak. To
install go to Toolsgt;Addins. 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: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=524961Of course, much simpler:

=EOMONTH(A1,0)-A1

--
Kind regards,

Niek Otten

quot;Niek Ottenquot; gt; wrote in message ...
gt; Hi Elf,
gt;
gt; Slightly less complicated:
gt;
gt; =DAY(EOMONTH(A1,0))-DAY(A1)
gt;
gt; This needs Analysis Toolpak to be installed. If you get a #NAME error:
gt;
gt; Toolsgt;Add-ins, check Analysis Toolpak
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;elfmajestyquot; gt; wrote in message ...
gt;gt; Hello!
gt;gt;
gt;gt; I need to write a formula that will calculate how many days are left in a
gt;gt; specific month and can't seem to get a working one. Seems like it should be
gt;gt; relatively easy.
gt;gt;
gt;gt; Example:
gt;gt;
gt;gt; Cell A1 contains date of 03/09/98.
gt;gt; I need cell B1 to contain the resulting number as 22 days remaining.
gt;gt;
gt;gt; Cell A2 contains date of 04/03/98
gt;gt; Cell B2 should contain the resulting number as 27 days remaining.
gt;gt;
gt;gt; I tried this:
gt;gt; =(DAY(DATE(YEAR(A2),MONTH(A2) 1,0))-(DAY(A2)))
gt;gt; Which actually works, but I was just wondering if there was a less
gt;gt; complicated way? Trying to explain this one to the person requesting the
gt;gt; spreadsheet may not be the easiest of jobs and I know I'll get questioned on
gt;gt; it.
gt;gt;
gt;gt; Any help would be appreciated!
gt;gt; Cheers,
gt;gt; Elf
gt;
gt;
Sorry about this one

--
Kind regards,

Niek Otten

quot;Niek Ottenquot; gt; wrote in message ...
gt; Hi Elf,
gt;
gt; =A1/24/60/60
gt;
gt; Format Custom as d:hh:mm:ss
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;elfmajestyquot; gt; wrote in message ...
gt;gt; Hello!
gt;gt;
gt;gt; I need to write a formula that will calculate how many days are left in a
gt;gt; specific month and can't seem to get a working one. Seems like it should be
gt;gt; relatively easy.
gt;gt;
gt;gt; Example:
gt;gt;
gt;gt; Cell A1 contains date of 03/09/98.
gt;gt; I need cell B1 to contain the resulting number as 22 days remaining.
gt;gt;
gt;gt; Cell A2 contains date of 04/03/98
gt;gt; Cell B2 should contain the resulting number as 27 days remaining.
gt;gt;
gt;gt; I tried this:
gt;gt; =(DAY(DATE(YEAR(A2),MONTH(A2) 1,0))-(DAY(A2)))
gt;gt; Which actually works, but I was just wondering if there was a less
gt;gt; complicated way? Trying to explain this one to the person requesting the
gt;gt; spreadsheet may not be the easiest of jobs and I know I'll get questioned on
gt;gt; it.
gt;gt;
gt;gt; Any help would be appreciated!
gt;gt; Cheers,
gt;gt; Elf
gt;
gt;
On Tue, 21 Mar 2006 11:21:27 -0800, elfmajesty
gt; wrote:

gt;Hello!
gt;
gt;I need to write a formula that will calculate how many days are left in a
gt;specific month and can't seem to get a working one. Seems like it should be
gt;relatively easy.
gt;
gt;Example:
gt;
gt;Cell A1 contains date of 03/09/98.
gt;I need cell B1 to contain the resulting number as 22 days remaining.
gt;
gt;Cell A2 contains date of 04/03/98
gt;Cell B2 should contain the resulting number as 27 days remaining.
gt;
gt;I tried this:
gt;=(DAY(DATE(YEAR(A2),MONTH(A2) 1,0))-(DAY(A2)))
gt;Which actually works, but I was just wondering if there was a less
gt;complicated way? Trying to explain this one to the person requesting the
gt;spreadsheet may not be the easiest of jobs and I know I'll get questioned on
gt;it.
gt;
gt;Any help would be appreciated!
gt;Cheers,
gt;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

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.ukquot;elfmajestyquot; gt; wrote in message
...
gt; Hello!
gt;
gt; I need to write a formula that will calculate how many days are left in a
gt; specific month and can't seem to get a working one. Seems like it should
gt; be
gt; relatively easy.
gt;
gt; Example:
gt;
gt; Cell A1 contains date of 03/09/98.
gt; I need cell B1 to contain the resulting number as 22 days remaining.
gt;
gt; Cell A2 contains date of 04/03/98
gt; Cell B2 should contain the resulting number as 27 days remaining.
gt;
gt; I tried this:
gt; =(DAY(DATE(YEAR(A2),MONTH(A2) 1,0))-(DAY(A2)))
gt; Which actually works, but I was just wondering if there was a less
gt; complicated way? Trying to explain this one to the person requesting the
gt; spreadsheet may not be the easiest of jobs and I know I'll get questioned
gt; on
gt; it.
gt;
gt; Any help would be appreciated!
gt; Cheers,
gt; Elf
gt; =32-DAY(A1)-DAY(A1-DAY(A1) 32)

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

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

lt;ggt;

--

Sandy
with @tiscali.co.uk
On Tue, 21 Mar 2006 21:07:27 -0000, quot;Sandy Mannquot; gt;
wrote:

gt;gt; =32-DAY(A1)-DAY(A1-DAY(A1) 32)
gt;
gt;gt;gt;=(DAY(DATE(YEAR(A2),MONTH(A2) 1,0))-(DAY(A2)))
gt;gt;gt;Which actually works, but I was just wondering if there was a less
gt;gt;gt;complicated way? Trying to explain this one to the person requesting the
gt;gt;gt;spreadsheet may not be the easiest of jobs and I know I'll get questioned
gt;gt;gt;on
gt;gt;gt;it.
gt;
gt;If Elf thought that he was going to have trouble explaining it
gt;before............
gt;
gt;lt;ggt;

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