Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
elfmajesty
 
Posts: n/a
Default Number of days left in the Month?

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default Number of days left in the Month?

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



  #3   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default Number of days left in the Month?

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



  #4   Report Post  
Posted to microsoft.public.excel.misc
SteveG
 
Posts: n/a
Default Number of days left in the Month?


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

  #5   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default Number of days left in the Month?

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







  #6   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default Number of days left in the Month?

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





  #7   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default Number of days left in the Month?

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
  #9   Report Post  
Posted to microsoft.public.excel.misc
Sandy Mann
 
Posts: n/a
Default Number of days left in the Month?

=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


  #10   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default Number of days left in the Month?

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
to find number of days between 2 dates using vba code in excel sjayar Excel Discussion (Misc queries) 3 November 3rd 05 07:24 AM
Need help to reflect number of days in a month. Lewis Koh Excel Worksheet Functions 8 July 30th 05 01:14 AM
Formula for current month minus one = Quarter number in a macro. Pank Excel Discussion (Misc queries) 11 June 22nd 05 02:47 PM
Calculating the number of Fridays in a month Greg Ward Excel Worksheet Functions 5 March 2nd 05 06:47 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 05:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"