Excel Formulas to Get Day of The Month
Here’s a basic but useful Excel formula. I haven’t been blogging much lately so want to create a few helpful posts. Noticed when I was sharing a more complicated formula that some of the smaller pieces might not be well understood. Basic DAY Function For Today’s Date Common Examples Date in Cell Formula Result 1/15/2025 =DAY(A1) 15 12/3/2024 =DAY(A1) 3 6/25/2025 =DAY(A1) 25 Text Format (if you want “01” instead of “1”) Extract Day from Text Date If your date is stored as text (like “June 25, 2025”): Multiple Cells To get the day from multiple date cells, just change the cell reference: Note:
Create Rank Number-Words (i.e. 1st, 5th, etc) in Excel
Was working on a Foxco data project recently where i needed to take dates that were ranked, add a value and then add the proper suffix to them as a number-word (i.e. the 1/1/2025 is the 1st day of the year) and 1/9/2025 is the 9th (not the 9st) day of the year. How this formula works, in simple terms (detailed explanation at the bottom). In my case, M2 was the cell reference contains the number I wanted to convert. The formula handles the special cases for 11th, 12th, and 13th (which use “th” instead of “st”, “nd”, “rd”) For all other numbers, it looks at the last digit to determine the suffix. Examples: 1 → 1st2 → 2nd3 → 3rd4 → 4th11 → 11th (not 11st)21 → 21st22 → 22nd23 → 23rd101 → 101st111 → 111th (not 111st) Here’s an alternative version that uses A1 and different but equally sound logic. Excel Ordinal Formula Breakdown (Nested IF Version) The Formula: excel=M2&IF(OR(MOD(M2,100)=11,MOD(M2,100)=12,MOD(M2,100)=13),”th”,IF(MOD(M2,10)=1,”st”,IF(MOD(M2,10)=2,”nd”,IF(MOD(M2,10)=3,”rd”,”th”)))) Step-by-Step Explanation: 1. M2& 2. MOD(M2,100) 3. OR(MOD(M2,100)=11, MOD(M2,100)=12, MOD(M2,100)=13) 4. First IF Statement: excelIF(OR condition, “th”, [nested IFs]) 5. MOD(M2,10) 6. The Nested IF Chain: excelIF(MOD(M2,10)=1,”st”, IF(MOD(M2,10)=2,”nd”, IF(MOD(M2,10)=3,”rd”,”th”))) This creates a decision tree: Visual Decision Flow: Examples: NumberLast 2 digitsSpecial case?Last digitSuffixResult11No1st1st22No2nd2nd33No3rd3rd44No4th4th1111Yes-th11th1212Yes-th12th1313Yes-th13th2121No1st21st2222No2nd22nd2323No3rd23rd11111Yes-th111th Key Differences from CHOOSE Version: Both formulas accomplish the same result, but the first version is more straightforward in its logic flow.