| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2478
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 |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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 http://forum.openoffice.org |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
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. |
That is why, your formula needs more information.
Sliderule |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
|
