Number of days left in the Month?

[elfmajesty]

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

[SteveG]

Elf,

You can use EOMONTH. You need to have the Analysis ToolPak. To install go to Tools>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

[Niek Otten]

Hi Elf,

Slightly less complicated:

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

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

Tools>Add-ins, check Analysis Toolpak

--
Kind regards,

Niek Otten

"elfmajesty" <[email protected]> wrote in message news:[email protected]...
> 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

[Niek Otten]

Hi Elf,

=A1/24/60/60

Format Custom as d:hh:mm:ss

--
Kind regards,

Niek Otten

"elfmajesty" <[email protected]> wrote in message news:[email protected]...
> 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

[Niek Otten]

Of course, much simpler:

=EOMONTH(A1,0)-A1

--
Kind regards,

Niek Otten

"Niek Otten" <[email protected]> wrote in message news:%[email protected]...
> Hi Elf,
>
> Slightly less complicated:
>
> =DAY(EOMONTH(A1,0))-DAY(A1)
>
> This needs Analysis Toolpak to be installed. If you get a #NAME error:
>
> Tools>Add-ins, check Analysis Toolpak
>
> --
> Kind regards,
>
> Niek Otten
>
> "elfmajesty" <[email protected]> wrote in message news:[email protected]...
>> 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
>
>

[Niek Otten]

Sorry about this one

--
Kind regards,

Niek Otten

"Niek Otten" <[email protected]> wrote in message news:[email protected]...
> Hi Elf,
>
> =A1/24/60/60
>
> Format Custom as d:hh:mm:ss
>
> --
> Kind regards,
>
> Niek Otten
>
> "elfmajesty" <[email protected]> wrote in message news:[email protected]...
>> 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
>
>

[Ron Rosenfeld]

On Tue, 21 Mar 2006 11:21:27 -0800, elfmajesty
<[email protected]> 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

[Sandy Mann]

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
[email protected]
[email protected] with @tiscali.co.uk


"elfmajesty" <[email protected]> wrote in message
news:[email protected]...
> 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

[Sandy Mann]

> =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
[email protected]
[email protected] with @tiscali.co.uk

[Ron Rosenfeld]

On Tue, 21 Mar 2006 21:07:27 -0000, "Sandy Mann" <[email protected]>
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

[Sandy Mann]

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
[email protected]
[email protected] with @tiscali.co.uk


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 21 Mar 2006 21:07:27 -0000, "Sandy Mann"
> <[email protected]>
> 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