Number of days in certain month | Days left in month

Topic Labels: Dates & Timezones
2252 2
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

Hi everyone. I am able to get the following using Excel formulas but unable to find the equivalent in Airtable formula. Would appreciate any help here:

[based on a given date e.g. 2020-11-25], show

  1. Number of days in a given month [30 days]
  2. Date of 1st day of this given month [1 Nov 2020]
  3. Days left in this given month [5]

I tried workdays formula to no avail.

Thank you.

2 Replies 2
8 - Airtable Astronomer
8 - Airtable Astronomer

Hi James!

That’s a fun question. Airtable’s formulas are a little different than Excel, but you can acheive the same results. Here’s how I went about it:

  1. find the first day of the month by finding the year and month, adding “01” for the day, and parsing them together:


  1. Find the last day of the month by adding one month to the first day of the month and then subtracting one day:

DATEADD(DATEADD({First Day of Month},1,'month'),-1,'day')

  1. Find the number of days in the month by finding what number day the last day is:

DATETIME_FORMAT({Last Day of Month},'D')

  1. Find the days left by finding the number of the current day and subtracting it from the number of days in the month:

{Days In Month} - DATETIME_FORMAT(Date,'D')

Here’s the base where I made these formulas:

And a very useful overview of date formatting in Airtable:

That’s amazing, Julian. Working perfectly with slight tweaks.
I am appreciative of the great help I am getting from this community!