OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

I know there is a simple solution......

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
JoeS
General User
General User


Joined: 27 Jan 2007
Posts: 5

PostPosted: Mon Jan 29, 2007 8:24 am    Post subject: I know there is a simple solution...... Reply with quote

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
View user's profile Send private message
probe1
Moderator
Moderator


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

PostPosted: Mon Jan 29, 2007 9:13 am    Post subject: Reply with quote

Code:
=getDaysInMonth(A1)

?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jan 29, 2007 11:06 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Mon Jan 29, 2007 11:58 am    Post subject: Reply with quote

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
View user's profile Send private message
JoeS
General User
General User


Joined: 27 Jan 2007
Posts: 5

PostPosted: Tue Jan 30, 2007 8:22 am    Post subject: Reply with quote

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
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Tue Jan 30, 2007 8:51 am    Post subject: Reply with quote

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? Wink

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
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Tue Jan 30, 2007 9:23 am    Post subject: Reply with quote

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
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Tue Jan 30, 2007 12:56 pm    Post subject: Reply with quote

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
View user's profile Send private message
JoeS
General User
General User


Joined: 27 Jan 2007
Posts: 5

PostPosted: Tue Jan 30, 2007 1:37 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group