# of days in that month for a selected date
What is the formula to return the # of days in that month for a randomly
selected date? Thanks for the help. |
# of days in that month for a selected date
As far as I know there is not a direct formula for this, however, by
combining several formulas it is possible to solve. Let's say the "randomly selected date" is in cell A1, then put this formula in A2 to display the number of days in that month: =EOMONTH(A1;0)-DATE(YEAR(A1);MONTH(A1);1)+1 This is what happens: EOMONTH(A1;0) returns the serial number of the last day in the moth of the date in A1 YEAR(A1) returns the year of the date in A1 MONTH(A1) returns tha month of the date in A1 DATE(YEAR(A1);MONTH(A1);1) returns the date with same year as A1, same month as A1, day 1 of that month if you subtract the last day of the month with the first day of the month and add 1 then you get the total number of days in that month NOTE: Excel will most probably automatically change the Number format of cell A2 to date-time in this case, which does not give you any useful output. Select A2, choose format cells and change number to "General" (or to "Number" with "decimal places" = 0) "Narnimar" wrote: What is the formula to return the # of days in that month for a randomly selected date? Thanks for the help. |
# of days in that month for a selected date
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
seems to me a little bit simpler and doesn't require Analysis Toolpak (EOMONTH does). You have to format the result cell as General in this case, too. Regards, Stefi €˛Valdus€¯ ezt Ć*rta: As far as I know there is not a direct formula for this, however, by combining several formulas it is possible to solve. Let's say the "randomly selected date" is in cell A1, then put this formula in A2 to display the number of days in that month: =EOMONTH(A1;0)-DATE(YEAR(A1);MONTH(A1);1)+1 This is what happens: EOMONTH(A1;0) returns the serial number of the last day in the moth of the date in A1 YEAR(A1) returns the year of the date in A1 MONTH(A1) returns tha month of the date in A1 DATE(YEAR(A1);MONTH(A1);1) returns the date with same year as A1, same month as A1, day 1 of that month if you subtract the last day of the month with the first day of the month and add 1 then you get the total number of days in that month NOTE: Excel will most probably automatically change the Number format of cell A2 to date-time in this case, which does not give you any useful output. Select A2, choose format cells and change number to "General" (or to "Number" with "decimal places" = 0) "Narnimar" wrote: What is the formula to return the # of days in that month for a randomly selected date? Thanks for the help. |
# of days in that month for a selected date
Hi Narnimar,
Here's another way: =DATE(YEAR(A1),MONTH(A1)+1,0)-DATE(YEAR(A1),MONTH(A1),0) -- Cheers macropod [MVP - Microsoft Word] "Narnimar" wrote in message ... What is the formula to return the # of days in that month for a randomly selected date? Thanks for the help. |
# of days in that month for a selected date
I copied it but Formula throws an error to me. Thanks to you to verify and reply. "Valdus" wrote: As far as I know there is not a direct formula for this, however, by combining several formulas it is possible to solve. Let's say the "randomly selected date" is in cell A1, then put this formula in A2 to display the number of days in that month: =EOMONTH(A1;0)-DATE(YEAR(A1);MONTH(A1);1)+1 This is what happens: EOMONTH(A1;0) returns the serial number of the last day in the moth of the date in A1 YEAR(A1) returns the year of the date in A1 MONTH(A1) returns tha month of the date in A1 DATE(YEAR(A1);MONTH(A1);1) returns the date with same year as A1, same month as A1, day 1 of that month if you subtract the last day of the month with the first day of the month and add 1 then you get the total number of days in that month NOTE: Excel will most probably automatically change the Number format of cell A2 to date-time in this case, which does not give you any useful output. Select A2, choose format cells and change number to "General" (or to "Number" with "decimal places" = 0) "Narnimar" wrote: What is the formula to return the # of days in that month for a randomly selected date? Thanks for the help. |
# of days in that month for a selected date
Maybe a bit shorter
=32-DAY(A1-DAY(A1)+32) Format as general Mike "Narnimar" wrote: What is the formula to return the # of days in that month for a randomly selected date? Thanks for the help. |
# of days in that month for a selected date
As Stefi mentioned EOMONTH needs Analysis Toolpak to be installed.
This is a very useful toolpak to have installed, but I think you have received a lot of help from the other users where you don't need it for this issue. "Narnimar" wrote: I copied it but Formula throws an error to me. Thanks to you to verify and reply. "Valdus" wrote: As far as I know there is not a direct formula for this, however, by combining several formulas it is possible to solve. Let's say the "randomly selected date" is in cell A1, then put this formula in A2 to display the number of days in that month: =EOMONTH(A1;0)-DATE(YEAR(A1);MONTH(A1);1)+1 This is what happens: EOMONTH(A1;0) returns the serial number of the last day in the moth of the date in A1 YEAR(A1) returns the year of the date in A1 MONTH(A1) returns tha month of the date in A1 DATE(YEAR(A1);MONTH(A1);1) returns the date with same year as A1, same month as A1, day 1 of that month if you subtract the last day of the month with the first day of the month and add 1 then you get the total number of days in that month NOTE: Excel will most probably automatically change the Number format of cell A2 to date-time in this case, which does not give you any useful output. Select A2, choose format cells and change number to "General" (or to "Number" with "decimal places" = 0) "Narnimar" wrote: What is the formula to return the # of days in that month for a randomly selected date? Thanks for the help. |
# of days in that month for a selected date
Try this
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) On Sep 19, 2:16*am, Narnimar wrote: What is the formula to return the # of days in that month *for a randomly selected date? Thanks for the help. |
# of days in that month for a selected date
This is very cute. Thanks a lot.
"Mike H" wrote: Maybe a bit shorter =32-DAY(A1-DAY(A1)+32) Format as general Mike "Narnimar" wrote: What is the formula to return the # of days in that month for a randomly selected date? Thanks for the help. |
# of days in that month for a selected date
Well I've rarely heard of a formula described as 'Cute' but there we are!!.
Thanks for the feedback. Mike "Narnimar" wrote: This is very cute. Thanks a lot. "Mike H" wrote: Maybe a bit shorter =32-DAY(A1-DAY(A1)+32) Format as general Mike "Narnimar" wrote: What is the formula to return the # of days in that month for a randomly selected date? Thanks for the help. |
All times are GMT +1. The time now is 06:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com