0

I have in the 6th row 12 column with the months of a year but it's an abbreviation; ex: Jan, Feb, Apr..., Dec. I want to count the days of the month in the cell just above it (7th row).

the abbreviation is a problem.

Thank you for your help.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Samy
  • 1

2 Answers2

4

Because the number of days per month changes depending on the year (since some years are leap years), I would recommend having years attached to your month. For example, write January 2023 instead of January. You can change how the cell displays the date afterwards by right-clicking your month cell, and then clicking "Format Cells".

To count the number of days in the month, you can just find the day number of the last day of the month (or, as the code below does, takes the first day of the next month and subtracts 1).

=DAY(DATE(YEAR(A1), MONTH(A1) + 1, 1) - 1)

Excel spreadsheet with the number of dates per month for 2021

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
asyndeton256
  • 41
  • 1
  • Same algorithm but a bit shorter: `=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))` *In Excel, the 0th day of a month is the last day of the previous month*. – Ron Rosenfeld Jun 26 '23 at 18:51
4

Many ways to do it, here are few alternatives, that would work with MS365 & earlier versions as well, assumption is taken that the default year is 2023, using EOMONTH( ) & DAY( ) functions.

enter image description here

• Formula used in cell B7

=DAY(EOMONTH(B6&" 1, "&2023,0))

• Formula used in cell B8

=DAY(EOMONTH(DATE(2023,MONTH(B6&1),1),0))

• Formula used in cell B9

=DAY(EOMONTH(B6:M6&" 1, "&2023,0))

Note: The first two formulas where you need to drag right while the last one, will spill right, so no need to drag. Also instead of hardcoding the year, you can use cell reference to make it dynamic, like as below.

enter image description here

Here is two alternatives as suggested by P.b

=DAY(DATE(2023,COLUMN(B1:M1),1)-1)

=DAY(EOMONTH(1&B6:M6&2023,0))

enter image description here

Caveat: Few alternatives depend on the Locale Date Settings

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
  • 1
    nice options, some are dependent on locale date notation, but the `B8` solution would work for any. We could also choose not to reference the cell at all: `=DAY(DATE(YEAR(NOW()),COLUMN(B1:M1),1)-1)` (note that NOW() is volatile, so better to use `=DAY(DATE(2023,COLUMN(B1:M1),1)-1)` in case of 2023. – P.b Jun 25 '23 at 07:33
  • 1
    Ah, good use of `COLUMN()` function. But OP has defined the month names already. Yes I avoid using volatile functions. – Mayukh Bhattacharya Jun 25 '23 at 07:39
  • 1
    I'd like to mention that my version is a tweak of the version asyndeton256 posted. I just added the COLUMN-reference as an option. – P.b Jun 25 '23 at 08:21
  • Another approach suggested @P.b --> `=DAY(EOMONTH(1&B6:M6&2023,0))` – Mayukh Bhattacharya Jun 25 '23 at 08:27