Need number of days for each month of the year!

Question

Level 1

4 points

Need number of days for each month of the year!

Hi,

So on Numbers for Mac I'm trying to make a spreadsheet where the user can select the month via a drop down menu, and then depending on the month selected, the table will know whether it is 28, 30 or 31 nights. It is a calculation for a rental property on a per night basis.

I have made the drop down for the months of the year but how can I link each month to the number of nights? So i kind of want to say: If 'January' is selected, then multiply by 31 and so on for each other month. Any help appreciated. Thanks

Posted on Jan 3, 2021 11:47 AM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,023 points

Jan 3, 2021 1:30 PM in response to DJSammy

Make a lookup table listing the months by name in column A and the number of days in that month listed in column B.

Then use LOOKUP, VLOOKUP or INDEX and MATCH to search for the monthname in column A and return the number of days from that row in column B.

This will require editing the list for leap years, then reversing the edit for the following year.

Formul shown as entered in selected cell. Filled down the last cell in column C le larger table.

Text version of formula for copy & paste to formula editor.

IF(B2="","",VLOOKUP(B2,Days::A:B,2))

Regards,

Barry

Reply

Answer 2

SGIII

Level 8

37,550 points

Jan 4, 2021 6:30 AM in response to DJSammy

You can do this in one cell without extra tables like this:

=DUR2DAYS(EDATE(A2,1)−A2)

Then when you make another choice in the Pop-Up the correct number of days for that month is automatically shown in B2, and you can base your calculations on it.

SG

Reply

Answer 3

Barry

Level 9

60,023 points

Jan 4, 2021 2:20 PM in response to SGIII

Hi SG,

"You can do this in one cell…"

Even better! :-)

Regrds,

Barry

Reply

Need number of days for each month of the year!

Similar questions