Help

Everyday this Year (autoupdating)

Topic Labels: Formulas
1477 10
cancel
Showing results for 
Search instead for 
Did you mean: 
Allison_Proctor
4 - Data Explorer
4 - Data Explorer

I’m trying to display the days in which a certain volunteer has come, as a percentage of the days that have passed this year. I’d like that to be shown in a block, i.e. 33.1% of days Volunteer X has been present, (178 days this year, X has been here for 59 of them). How can I create such a block? How can I create a column which counts the days of the year that have passed?

10 Replies 10
AlliAlosa
10 - Mercury
10 - Mercury

Hi there! This might help to get you started. The following formula should show how many days into the year we are as of today :slightly_smiling_face:

DATETIME_DIFF(TODAY(), DATETIME_PARSE("1/1/" & YEAR(TODAY())), 'days')

Edit: the above will show 177 as of today, if you want to count today as well, add a “+ 1” onto the end

Thank you so much! One last question to finish the job; how can I divide the total number of days in the year by the total number of entries? To clarify, I have a perfectly functioning “Days in the Year” field (thanks to you!), as well as 59 “unique” date entries. I would like to divide the “Days in the Year” field by the “unique” date entries, in a formula category, and then display this as a percentage, in a block. I appreciate all the help thus far!

I attempted {Days this Year}/ARRAYUNIQUE(Date) and still got errors.

It’s hard to say without knowing how your base is set up. Are each of the date entries linked to a volunteer on another table? If so, I would do this…

On the same table as the date entries, add a field with the formula:

IF(YEAR(TODAY()) = YEAR({Date Field}), 1)

Then on your volunteer table, create a rollup field that’s pointed at the new field you just made with the following function…

SUM(values)/DATETIME_DIFF(TODAY(), DATETIME_PARSE("1/1/" & YEAR(TODAY())), 'days')

Set the formatting to be a percentage, and that should do it! Note that this approach actually doesn’t use the field {Days in the Year}; instead it’s built directly into the rollup. If you want to keep that field separate so you can see what the number actually is, change the function to be…

SUM(values)/{Days in the Year}
Allison_Proctor
4 - Data Explorer
4 - Data Explorer

Hmmm. Thank you very much for your response! Unfortunately my base is set up differently, but your efforts are truly appreciated!

As for my base:

A Date column is established in which 2-5 entries are placed on each day that Volunteer X is present. So there are about 235 entries, on roughly 59 “unique” days. There is no category for which volunteer completed any of the tasks reported.

So for maximum clarity; there is a date column that shows the 59 days Volunteer X has been with me. Within each of those 59 dates, there are multiple entries, which explain the particular tasks Volunteer X completed. My objective is to display a percentage block, in which those 59 days (when the Volunteer is present) are divided by the total number of Days this Year (178). This percentage would shift as more days passed, and as Volunteer X was present for more days.

The 59 is already described as the “Summary Type: Unique” within the “Date” field, given there are 59 unique dates, each with a variety of entries.

I really hope that helps. Thank you so much for your efforts!

Hmmm… Thank you for the details - I’m still having just a little trouble envisioning how your base is set up. :frowning: How many tables do you have? Can you describe how they are linked?

If you’re comfortable sharing a link to your base or a “scrubbed” copy of it, I’m happy to take a look at it. You can message it to me directly if you prefer!

Allison_Proctor
4 - Data Explorer
4 - Data Explorer

Yeah that sounds perfect! How do I create a “scrubbed” copy?

That’s entirely up to you :slightly_smiling_face: It’s just removing any information that could be deemed sensitive or you’re otherwise not comfortable with other eyes seeing. If it were me, I would change names/any other identifying info.

Allison_Proctor
4 - Data Explorer
4 - Data Explorer

Alrighty, I’ve created a copy I find acceptable and am ready to share it with you! Thank you so much for your help, once again. Would you prefer I send it to your email, or is there a way to direct message on this site?

Awesome! I sent you a direct message.