Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Work out How Many Days are in a Month
Just wondering if anyone has any idea how to find out how many days are in a
given month from a given year? So If I were to put the month as APRIL 2008 I'd get back 30. Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Work out How Many Days are in a Month
With a date in a1 try
=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1) Mike "NateBuckley" wrote: Just wondering if anyone has any idea how to find out how many days are in a given month from a given year? So If I were to put the month as APRIL 2008 I'd get back 30. Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Work out How Many Days are in a Month
Credit to Ron Rosenfeld for this one... Here is a VBA implementation
of his worksheet function: Sub daysinMth() Dim WhichDate As Date Dim Days As Integer WhichDate = DateSerial(2008, 2, 12) Days = 32 - Day(WhichDate - Day(WhichDate) + 32) MsgBox Days & " in " & WhichDate End Sub PS. You did say you wanted a VBA version? Cheers, Ivan. On Apr 3, 9:52*pm, Mike H wrote: With a date in a1 try =DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1) Mike "NateBuckley" wrote: Just wondering if anyone has any idea how to find out how many days are in a given month from a given year? So If I were to put the month as APRIL 2008 I'd get back 30. Thanks in advance!- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Work out How Many Days are in a Month
Ah you said VBA
Public Function DaysInMonth(myMonth As Long) As Long DaysInMonth = Day(DateSerial(Year(Date), myMonth + 1, 1) - 1) End Function Call with =DaysInMonth(Month(a1)) with a date in A1 Mike "NateBuckley" wrote: Just wondering if anyone has any idea how to find out how many days are in a given month from a given year? So If I were to put the month as APRIL 2008 I'd get back 30. Thanks in advance! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Work out How Many Days are in a Month
On Apr 3, 10:06*pm, Mike H wrote:
Ah you said VBA Public Function DaysInMonth(myMonth As Long) As Long * * DaysInMonth = Day(DateSerial(Year(Date), myMonth + 1, 1) - 1) End Function Call with =DaysInMonth(Month(a1)) with a date in A1 Mike "NateBuckley" wrote: Just wondering if anyone has any idea how to find out how many days are in a given month from a given year? So If I were to put the month as APRIL 2008 I'd get back 30. Thanks in advance!- Hide quoted text - - Show quoted text - Hi, Jusa another variation... If we read your post literally, this version of the function should handle a literal string as well. Actually, it should handle pretty much anything. If you feed it something that can't possibly be recognised as some sort of date, it will just give a #VALUE error: Function daysinMth(MonthYr As Variant) If VarType(MonthYr) = vbString Then _ MonthYr = DateValue("1 " & MonthYr) daysinMth = 32 - Day(MonthYr - Day(MonthYr) + 32) End Function Cheers, Ivan. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Work out How Many Days are in a Month
Thanks mate, both worked a treat, I shall have a play around.
Thank you. "Ivyleaf" wrote: On Apr 3, 10:06 pm, Mike H wrote: Ah you said VBA Public Function DaysInMonth(myMonth As Long) As Long DaysInMonth = Day(DateSerial(Year(Date), myMonth + 1, 1) - 1) End Function Call with =DaysInMonth(Month(a1)) with a date in A1 Mike "NateBuckley" wrote: Just wondering if anyone has any idea how to find out how many days are in a given month from a given year? So If I were to put the month as APRIL 2008 I'd get back 30. Thanks in advance!- Hide quoted text - - Show quoted text - Hi, Jusa another variation... If we read your post literally, this version of the function should handle a literal string as well. Actually, it should handle pretty much anything. If you feed it something that can't possibly be recognised as some sort of date, it will just give a #VALUE error: Function daysinMth(MonthYr As Variant) If VarType(MonthYr) = vbString Then _ MonthYr = DateValue("1 " & MonthYr) daysinMth = 32 - Day(MonthYr - Day(MonthYr) + 32) End Function Cheers, Ivan. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Work out How Many Days are in a Month
Cheers matey I shall check it out now.
Thanks! "Mike H" wrote: Ah you said VBA Public Function DaysInMonth(myMonth As Long) As Long DaysInMonth = Day(DateSerial(Year(Date), myMonth + 1, 1) - 1) End Function Call with =DaysInMonth(Month(a1)) with a date in A1 Mike "NateBuckley" wrote: Just wondering if anyone has any idea how to find out how many days are in a given month from a given year? So If I were to put the month as APRIL 2008 I'd get back 30. Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How many days in a row does an employee work within a 2 month peri | Excel Discussion (Misc queries) | |||
excel to make the days cary over month to month automaticly | New Users to Excel | |||
Number of days in month counted from shortened name of month & yea | Excel Worksheet Functions | |||
Days per month for calculating storage days | Excel Worksheet Functions | |||
Number of work days/month | Excel Worksheet Functions |