Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number of days left in the Month?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to find number of days between 2 dates using vba code in excel | Excel Discussion (Misc queries) | |||
Need help to reflect number of days in a month. | Excel Worksheet Functions | |||
Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) | |||
Calculating the number of Fridays in a month | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |