[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
JoeS
General User

Joined: 27 Jan 2007
Posts: 5

 Posted: Mon Jan 29, 2007 8:24 am    Post subject: I know there is a simple solution...... I have a drop down list in A1 with the months of the year. In B1, I want to be able to show the number of days in the month for the month chosen in A1. I have tried different formulas but I keep getting errors. Thank you in advance. Best Regards, JoeS
probe1
Moderator

Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

Posted: Mon Jan 29, 2007 9:13 am    Post subject:

 Code: =getDaysInMonth(A1)

?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Mon Jan 29, 2007 11:06 am    Post subject: If A1 is a string, I believe you need a two-column helper: January 1 February 2 ... December 12 B1: =VLOOKUP(\$A\$1;MonthMap;2;0) [MonthMap is the helper] C1: =DATE(YEAR(TODAY());\$B1+1;1) [1st of next month] D1: =DATE(YEAR(TODAY());\$B1;1) [1st of this month] E1: =\$C1-\$D1 [difference] Written as one formula: =DATE(YEAR(TODAY());VLOOKUP(\$A\$1;MonthMap;2;0)+1;1)-DATE(YEAR(TODAY());VLOOKUP(\$A\$1;MonthMap;2;0);1)_________________Rest in peace, oooforum.org Get help on https://forum.openoffice.org
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Mon Jan 29, 2007 11:58 am    Post subject: If the cell was a real date, you'd just use the DAYSINMONTH() function (you might need to add it into Calc - see the Help) but if it's just text, Villeroy's solution seems the best.
JoeS
General User

Joined: 27 Jan 2007
Posts: 5

 Posted: Tue Jan 30, 2007 8:22 am    Post subject: Maybe I haven't explained myself correctly. What I want to show is the number of days for the month chosen from the drop down list in A1 (ie if January is chosen, 31 would be shown in B1, if February is chosen, 28 would be displayed. The formula =getdaysinmonth(a1) does not work for me. =DAYSINMONTH returns the current month's total, not the one chosen from the drop down list in A1 )unless I'm doing something wrong. Hope this helps. Best Regards, JoeS
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

Posted: Tue Jan 30, 2007 8:51 am    Post subject:

You only need a small part of Villeroy's suggestion of you only want the number:

 Villeroy wrote: If A1 is a string, I believe you need a two-column helper: January 1 February 2 ... December 12 B1: =VLOOKUP(\$A\$1;MonthMap;2;0) [MonthMap is the helper]

If you don't want to use the named range MonthMap, use the cell range say B1:C12 or wherever you put the two-column helper.

What will you do in a Leap Year?

If you want the user to only be able to select a month from a drop-down list, use Data->Validity, and set the range to be B1:B12 or wherever you put the January to December list.
Sliderule
Super User

Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

Posted: Tue Jan 30, 2007 9:23 am    Post subject:

JoeS:

You said:
 JoeS wrote: What I want to show is the number of days for the month chosen from the drop down list in A1 (ie if January is chosen, 31 would be shown in B1, if February is chosen, 28 would be displayed.

But, what if it is Leap Year ( 2000, 2004, 2008 etc ). . . if you ONLY instruct the function a month name, withOUT the year, returning 28 for February, it may be wrong.

According to Calc Help:

 Calc Help DAYSINMONTH function wrote: DAYSINMONTH Calculates the number of days of the month in which the date entered occurs. Syntax DAYSINMONTH(Date) Date: Any date in the respective month of the desired year. The Date parameter must be a valid date according to the locale settings of OpenOffice.org. Example DAYSINMONTH(A1) returns 29 days if A1 contains 2/17/68, a valid date for February 1968.

Sliderule
Sliderule
Super User

Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

Posted: Tue Jan 30, 2007 12:56 pm    Post subject:

JoeS:

Perhaps, the image below will help. It is showing, for a list of month in Column A, the numer of days for the months . . . BUT . . . it 'assumes' the year is 2007 ( hard coded ), so, will NOT handle 'leap year'.

In the example you gave, with the "drop down list" in A1 . . . the following code would work:
 Code: =DAYSINMONTH( DATE("2007"; MONTH(A1 & " 1, " & "2007"); 1) )

The formula is asking for the DAYSINMONTH . . . determined by a DATE, defined as year "2007", MONTH taken from the date, calculated by the text string concatenation of Month in A1 & " 1, " & "2007" ( for instance January 1, 2007 ).

Below is a display of the month name in Column A, the result ( # of Days ) in Column B, and, the formula for determining the number of days in Column C.

I hope this helps, please be sure to let me / us know.

Sliderule
JoeS
General User

Joined: 27 Jan 2007
Posts: 5

 Posted: Tue Jan 30, 2007 1:37 pm    Post subject: Sliderule, Your code worked like a charm. I also set it up as Villeroy suggested and it also works great ( abit more work but worth it). I appreciate all you who helped me understand this better. Best Regards, Joe
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum