Question

I have 2 date columns in format DD/MM/YYYY - 1 column contains Premium Pmt date (PPD) & the Other column is Today date (TODAY).

I want to do the following:

Split PPD into DD/MMM ; such that the Year component goes away completely (is not stored).

Split TODAY into DD/MMM ; same as above.

Calculate DIFFERENCE between Split PPD - Split TODAY

I did the following - Used formatDate to split the 2 columns into DD/MMM

The problem - When I am using dateBetween & inputting formatDate output as dates, I found out that it converts date into string and is accepting these inputs.

Can you please help me. My purpose is to remove year from the 2 columns and then compare the two just on basis of DD/MMM.

Attached screenshot below -

See Image.

Comments

what's your expected output?

if everything is working correctly what would you want it to show?

you can use toNumber() to change the string format back to number if that's what you're struggling with.

---

My expected output is the difference between PPD(Eg. 01-Sept) and today's date(Eg. 11-Oct) which comes to 40.

Notice that I have discarded the dates in PPD and today's date to make the output.

Thats what I want Notion to do.

---

Hey @thomas_yang - any update for me please?

Answer 1

Hey @Martin_SystemsHill

With a little bit of help from another user on Reddit, I figured out a way to achieve this. Basically you need to create custom variables and assign value to each, then join them using parseDate.

Created a sheet here with formula and some sample data to showcase it -
https://wheat-form-601.notion.site/Insurance-formula-1e2ce1811c434198a87898ee62654193?pvs=4

Answer 2

Hey, the best approach is to calculate the difference first before changing it to your desired formate.

Use the dateBetween() for the number of days calculation and then change the format. If you are trying to calculate the number of days between two dated regardless of the year such as 1-Jan-2023 and 10-Jan-2020 would still give you result of 9 just let me know and I will create a formula for you to do that.