Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Create Bar Chart from Date Fields

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Interface Designer

Solved

Jump to Solution

0
977
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 16, 2024 04:30 AM

Dear AT Community!

I'm collecting arrival and departure dates for an event setup period from workers. They can register through a form. In my base now I have two date fields with an arrival and a departure date for each worker. The setup period spans from 5th May to 16th May.

The question is: How many workers are on site each day?

In order to answer that question I created 8 formula fields and wrote a simple formula to extract the needed information from the date fields. The output looks like this:

Now in the bottom row the sums are given which equal the number of workers on site each day.

I tried to create a bar chart in an interface showing the days on the vertical axis and the number of workers on the horizontal axis. To no avail. Any ideas on how to approach this would be appreciated. I'm kinda stuck.

Best regards

Henry

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 16, 2024 08:38 PM

Hi,

Your approach is not good, because you need to create extra field for every next day.

Better solution is to create the table of days. You can put first date in the primary field of the first record, then put next day in the second record,

then select these two cells,

then click on little square at right-down of the cell and drag it down to the number of days you need

Link this table to a table of workers. Create link field, then add first worker, then copy the cell and paste it to whole field. (I just took random table of values and 30 days of this month, to act as test data)

Ctrl+C

CTRL+V

And now switch to workers table and do the same:

So, all workers will be linked to all dates

btw, add duration field,

`DATETIME_DIFF(end,start,'days')`

Now, make formula to extract interval. I renamed 'Link to Days' to ALL_DATES to avoid mess in formula. So, we will find {starting date} and take that part, up to {end date}

At first, I did complex formula with FIND {start}, ALL_DATES and FIND {END} etc, but then realized function MID needs start position and char count. it doesn't need end position. And chart count can be taken from {duration}, ISO date is always 10 chars, plus comma and space. SO:

`MID(ALL_DATES,(FIND(DATESTR(start),ALL_DATES)),12*duration)`

looks nice:

And the last thing, find how much given string included in a whole text (ROLLUP of Dates present). Substitute all occurences by '' , Substract LEN froma LEN of full text. Divide by the length of given string (10 in our case)

```
(LEN(ARRAYJOIN(values))-
LEN(SUBSTITUTE(ARRAYJOIN(values), DATESTR(Day), '')))/10
```

The advantage of this solution is ability to scale at any list of workers and list of dates, the only thing to be provided is correct linking - formulas will do the rest. Of course, if you take whole year and a list of 1000 people, such amount of links/formulas might crush the performance or hang the table.

But that's another story, and now chart can be created:

Reply

4 Replies 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 16, 2024 05:48 AM

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 16, 2024 08:38 PM

Hi,

Your approach is not good, because you need to create extra field for every next day.

Better solution is to create the table of days. You can put first date in the primary field of the first record, then put next day in the second record,

then select these two cells,

then click on little square at right-down of the cell and drag it down to the number of days you need

Link this table to a table of workers. Create link field, then add first worker, then copy the cell and paste it to whole field. (I just took random table of values and 30 days of this month, to act as test data)

Ctrl+C

CTRL+V

And now switch to workers table and do the same:

So, all workers will be linked to all dates

btw, add duration field,

`DATETIME_DIFF(end,start,'days')`

Now, make formula to extract interval. I renamed 'Link to Days' to ALL_DATES to avoid mess in formula. So, we will find {starting date} and take that part, up to {end date}

At first, I did complex formula with FIND {start}, ALL_DATES and FIND {END} etc, but then realized function MID needs start position and char count. it doesn't need end position. And chart count can be taken from {duration}, ISO date is always 10 chars, plus comma and space. SO:

`MID(ALL_DATES,(FIND(DATESTR(start),ALL_DATES)),12*duration)`

looks nice:

And the last thing, find how much given string included in a whole text (ROLLUP of Dates present). Substitute all occurences by '' , Substract LEN froma LEN of full text. Divide by the length of given string (10 in our case)

```
(LEN(ARRAYJOIN(values))-
LEN(SUBSTITUTE(ARRAYJOIN(values), DATESTR(Day), '')))/10
```

The advantage of this solution is ability to scale at any list of workers and list of dates, the only thing to be provided is correct linking - formulas will do the rest. Of course, if you take whole year and a list of 1000 people, such amount of links/formulas might crush the performance or hang the table.

But that's another story, and now chart can be created:

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 17, 2024 09:49 AM

Dear Alexey!

Thanks for the elaborate answer. This does the job. I was hoping to deal with this issue within the given table rather than creating a new one. There are a few hundred workers already registered and creating the links to the new table manually is not an option. I know an automation will do the job. It's just a bit more of a hassle than originally expected 😉

Thanks again for your help and for pointing me in the right direction.

Best regards

Henry

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 18, 2024 04:10 AM - edited Apr 18, 2024 04:15 AM

*There are a few hundred workers already registered and creating the links to the new table manually is not an option. I know an automation will do the job. It's just a bit more of a hassle than originally expected *

I feel that my solution is not the best and the task can be solved in some easier way.

But it's usual approach when you need to count totals, or create a pivot table or something like it. Formulas can do calculations within their rows, in 'horizontal' way, but for 'vertical' operations you need a linked table, because records in a single table 'doesn't know about each other'.

I often do complex inter-table connections and no matter how much records are in tables, it's just two copy-paste operations needed to connect them all.

My* switch to workers table and do the same *means 'select one worker with all dates linked, copy linked cell by Ctrl+C, select the whole column and do the paste by Ctrl+V' so it will link all dates to all workers.

But of course, if your case includes possible new workers, thing become harder, as every new record must be connected by link in the same way as others.

Anyway, I'm glad it helped you.