Duration between two dates with anything over 7 days showing as weeks

1

Hi friends!

I’m brand new to Coda switching over from Notion for my event rental business!

My pricing is based on rental lengths and there is a slight discount for renting for a week or more. So I would like to calculate the duration between two date columns - the start date and the end date. I’ve got this part working using [thisRow.End Date - thisRow.Start Date].

My question is, is there a way to have the output show as “X days” if it’s 6 days or less and as “X Weeks, X Days” if it is 7 days or more?

1 Like
2

Hi Caitlyn,

Welcome to the community! As someone who also switched from Notion a few years ago, I’m pretty sure you won’t regret it

I’m afraid the biggest time unit for ‘Duration’ columns is ‘Days’, so not possible to define weeks there.

image
image386×380 11.2 KB

But fear not! This is Coda and there’s a work around for almost anything. The only downside is that in my demo, the duration in weeks is only a text, so you can’t do calculations with it and should be used only for display.

Hope this helps,

Pablo

3 Likes
3

As a person whose work relies heavily on measuring the length of a pregnancy, the option for “weeks, days” would be amazing.

4

hi @Caitlyn_Fitzgerald ,

a variation on the @Pablo_DV contribution is this (outputting the same values):

SwitchIf(
  thisRow.Duration < 7, thisRow.Duration,
  Format("{1} {2} {3}",
   Floor(thisRow.Duration / 7),
    If(Floor(thisRow.Duration / 7) = 1,
      "week","weeks"),
   SwitchIf(
   thisRow.Duration.Remainder(7) = 0,"",
   thisRow.Duration.Remainder(7) = 1,
   Concatenate("1 day"),
   Concatenate(thisRow.Duration.Remainder(7),' days'))))

I was wondering about splitting up longer periods in months, weeks and days. The base logic goes like this for display reasons only.

SwitchIf(
  thisRow.Duration < 7, thisRow.Duration,
  thisRow.Duration <    thisRow.Date1.RelativeDate(1) - thisRow.Date1,
  "day and week logic",
  "day, week and months logic")

Cheers, Christiaan

2 Likes
5

You could also check out my pack Delta Date!

Here’s a working example for your use-case :pray:

5 Likes
7

Thank you so much! I will definitely check this out!

8

Thank you so much @Pablo_DV @Christiaan_Huizer! I appreciate you taking the time!