Notion Answers

Help between Notion users


Register & Ask

It's free & easy

Get answers

Answers, votes & comments

Vote and select answers

Receive points, vote and give the solution

Question

1vote

Help with Date formatting please!

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.

0vote

thomas_yang commented

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.

0vote

mayank_2323 commented

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.

0vote

mayank_2323 commented

Hey @thomas_yang - any update for me please?

2 Answers

0vote

mayank_2323 Points260

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

0vote

Martin_SystemsHill commented

Nice! Great Job. :)

2votes

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.

Please log in or register to answer this question.

...

Welcome to Notion Answers, where you can ask questions and receive answers from other members of the community.

Please share to grow the Notion Community!

Connect