Skip to main content

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.

jamesquek

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:


DATETIME_PARSE(DATETIME_FORMAT(Date,'YYYY') & DATETIME_FORMAT(Date,'MM') & '01','YYYYMMDD')



  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: https://airtable.com/invite/l?inviteId=invAGgFDa1m3xz4Ix&inviteToken=1941c133a29e2c9439cc26d23ed92df97fe2548c5384157fce529a914613e88f


And a very useful overview of date formatting in Airtable: https://support.airtable.com/hc/en-us/articles/216141218-Supported-format-specifiers-for-DATETIME-FORMAT

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:


DATETIME_PARSE(DATETIME_FORMAT(Date,'YYYY') & DATETIME_FORMAT(Date,'MM') & '01','YYYYMMDD')



  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: https://airtable.com/invite/l?inviteId=invAGgFDa1m3xz4Ix&inviteToken=1941c133a29e2c9439cc26d23ed92df97fe2548c5384157fce529a914613e88f


And a very useful overview of date formatting in Airtable: https://support.airtable.com/hc/en-us/articles/216141218-Supported-format-specifiers-for-DATETIME-FORMAT


oSOLVED]

That’s amazing, Julian. Working perfectly with slight tweaks.

I am appreciative of the great help I am getting from this community!


jamesquek

Reply


Terms & Conditions