Question

Hi,

In my calendar, I have a copied a formula to track the day's progress. However, it is only based on the hour without taking past, current, or future days into account. My database includes formulas that return a checkbox for if the day is today, if it has passed, or if it is upcoming.
The progress formula is:

if(10 * hour(now()) / 24 < 1, "▒░░░░░░░░░ " + format(floor(100 * hour(now()) / 24)) + "%", slice("▒▒▒▒▒▒▒▒▒▒", 0, 10 * hour(now()) / 24) + slice("░░░░░░░░░░", 10 * hour(now()) / 24) + " " + format(floor(100 * hour(now()) / 24)) + "%")

I would like to add the following conditions: if the day has passed, then the progress will read 100%; if the day is upcoming, the progress will read 0%.

I would really appreciate it if someone who has a better grasp on conditional statement could help me with the formula.

Thanks,
Jack

Answer 1

You can get creative with nesting "if" statements for this! When nesting "if" formulas, think of arranging them like a cascading effect.

The example in Notion will help get us started:

if(false, "yes", "no") == "no"

When we're nesting "if" statements, we can replace the "no" result with another question. So in your case, we're looking for specific results when:

• Today is before a date (0%)
• Today is after a date (100%)
• Today is the same as a date (live progress bar)

I like to use checkboxes as "indicators" for my if statements rather than baking everything into one formula. If you have one checkbox for "Before" and one for "After" a set date, we can set "if" statements cascading through the checkboxes. We can structure the formula like this:

Before formula:

if(formatDate(prop("Date"), "YYYYMMDD") **<** formatDate(now(), "YYYYMMDD"), true, false)

After formula:

if(formatDate(prop("Date"), "YYYYMMDD") **>** formatDate(now(), "YYYYMMDD"), true, false)

Progress Bar formula:

if(prop("Before") == true, "0%", if(prop("After") == true, "100%", <progress bar formula without "if" statements>))

The logic for this formula sort of works like this:

1. If "Before" is checked, always display "0%"
2. If "Before" is not checked, see if "After" is checked. If it is, display "100%"
3. If neither "Before" or "After" is checked, perform the progress bar formula.

I hope this helps! Let me know if you have any questions.

Here's a link to a page w/ the formulas in action:https://www.notion.so/jackhentosh/Hourly-Progress-Bar-d324a815afe44a3da15d06fc9a0853ce