If a date is between 2 dates, then this, else...?

1

Hi coda people!

I’m trying to make a billing document for me, and I’m a little stuck on one field.

I have a column which is 'Last Paid" - a date field. I have another column ‘Pay Period’ which I’d like to create a formula for. If Last Paid is after the 14th of x month, but before the 15th of y month, then 'Pay Period x - y" would fill in the Pay Period column.

I had this set up
“If(thisRow.[Last Paid]>Date(2021,10,14).And(thisRow.[Last Paid]>Date(2021,11,14)),“Pay Period 15 Oct - 14 Nov 2021”,”"), however, it fills in the same thing for every field no matter that the date is. Any help is appreciated :slight_smile:

2

hi Nikki, we did not see you for a while, good to have you back. I’d like to propose a dynamic solution like below. The 14 is hard coded, but of course you can replace this by a variable by referencing a column that holds the day number.

image
image1168×1756 220 KB

image
image2539×649 126 KB

below the code tha handles the months.

If(thisRow.paydate.Day() < 14, Format("Pay Period {1} 14 - {2} 15",thisRow.paydate.EndOfMonth(-1).MonthName(), thisRow.paydate.EndOfMonth(0).MonthName()),Format("Pay Period {1} 14 - {2} 15",thisRow.paydate.EndOfMonth(0).MonthName(), thisRow.paydate.EndOfMonth(1).MonthName()))

enjoy your weekend, Christiaan

1 Like
3

You have two errors in your formula. You’re writing .And() to the result of Date(...), not the comparison. Also you have your second comparison operator wrong: > instead of <.

It should’ve been
If((thisRow.[Last Paid] > Date(2021,10,14)).And(thisRow.[Last Paid] < Date(2021,11,14))

However —

Using logical functions such as Or() and And() is discouraged in Coda. It is better and more efficient to use logical operators instead:

If(
  thisRow.[Last Paid] > Date(2021,10,14)
  AND thisRow.[Last Paid] < Date(2021,11,14),
  "Pay Period 15 Oct - 14 Nov 2021",
  ""
)

And yeah, in general a dynamic solution would be better. Not like Christian suggested, but with a table of periods. Examine this sample:

P.S. Oh, I just noticed the “after the 15th” requirement. Well, you could adjust my solution to automatically pre-fill period dates as well.

2 Likes
4

Thanks so much Christiaan, you’re always so helpful!

1 Like
5

Hi, @Paul_Danyliuk. Why is that?

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.