Displaying Birthdays

Ask for Help
1

Hi there!

I have a list of members of a community and I want to show a birthday calendar. Showing one’s bday on a profile page would also be nice. Maybe even an alert if it is today.

There once was an app called “confetti” but I cannot find it anymore.

Do you have an idea how to do this?

The user will enter her birthday, but this will be saved as dateandtime. I do not want to output the time and I do not want to output the year of birth neither - it should be the actual year or sth like “in 4 weeks”.

Could I do it with a math column under "data? I do not want to do excel formulas :wink:

Thx
Jan

2

You just need to do some spreadsheet magic. This should do the job:

3

But glide is writing kind of this data to my cells:
1979-09-27T00:00:00.000Zz

User provides 27/09/1979 via datepicker

4

Oh, to answer your question no you can’t do it with the Glide Math column yet as far as I know so you are stuck with Google Sheets for now.

Just create a column in your sheet to format it the way you want it. For instance I added this to the sheet I shared:

={"Formatted nicely";ARRAYFORMULA(IF(LEN(B2:B),TEXT(B2:B,"mm/dd"),""))}
5

Hi George, thank you.

Maybe you can make your sheet writeable? Then I could add “my” data".

My problem is the data glide writes. Your formulas do not apply there and are throwing errors.

I still do not understand the way glide is writing to the sheet. How do I influence that?
Can I somehow control it? Do I have to prepare the column somehow?

I have to use the datepicker component, right? Why is there a timestamp written at all?

I now do have 2 birthday cells with 2 different data written from testing:

1979-09-27T00:00:00.000Z
and
27.09.1979

I do not know how this happened :slightly_smiling_face:

Jan

6

Both are dates in the sheet, they are just formatted differently. I wouldn’t worry about it as you should format them in a different column as you want using the TEXT() function.

If you only want the Month and Year, why not just ask for that. Here is a little app I created that shows you both ways. I set it up as a template so you can make a copy.

birthday.glideapp.io

7

Thanks! This works and I will be able to copy it.

But I still do not understand how the date component is behaving…

I also want to make a calendar tab with upcoming birthday (using your birthday this year column), then glide puts a time of “12:00”.

Seems that it is not possible to use date component without time?

Jan

8

I would venture to say that any system that stores dates stores them as a datetime object. If only a date is supplied then the time is set to 00:00:00 which is 12AM or 00:00. That is the way they are stored in a Google sheet as well.

The calendar view is designed for events with a time attached. It shows the day and then any events for that day. The time that is shown can not be suppressed. So for your own calendar view just create another Tab page that points to the Sheet1 but sorts on the Birthday a list view that is sorted by “Birthday this year” column. You can display whatever info you want to make it look like a calendar view. I did this in the sample app to give you an idea.

1 Like
9

George, thanks a lot!

One last question: would it make sense to have all birthdays in a separate sheet and build a relation?

Jan

10

I wouldn’t think so. Since people only have one birthday then all you need is an extra column in there row of other profile info. Relations mostly only make sense if there are more than one. There is a place for one to one relations but for me that has to do with the ability to have two different detail view screens pointing to somewhat of the same data that potentially allow editing of different areas of the data. A little hard to explain but as a general rule relations make more sense when it’s one to many.

Also if you make a relation just for birthdays you double the row count which brings you to needing a Pro version twice as fast.

11

Hi ok thanx.

I am not able to copy your formulas to my existing sheet, they still throw an “Formula parse error.”

And glide is again writing the strange timestamp format into the sheet: 1979-09-28T00:00:00.000Z

Your example is not doing this

Jan

12

Format that entire column as a more friendly Date format like mm/dd/yyyy. Does that work? This would be done by clicking the entire column in the sheet, then Format/Number/. pick a date format.

13

Yes, that works. If I try the data in your example it works very well, too. I am just not able to copy the formula to my sheet - somehow it breaks, but I cannot debug it.

Even if I copy the formula back and forth - it still works in the original sheet, but not in mine. Is there any chance to get a better error description?

14

You might have to alter the formula a bit, so it is pointing to the correct column. For example, change B2:B to whatever column has your date.

15

Yes sure! Copying to a new sheet also works, but it is not possible to copy it to my existing app sheet.

16

Can you take a screenshot of your Google sheet with the formula showing in the formula bar?

Also make sure the column cells below your formula are empty.

17

Bildschirmfoto 2020-03-05 um 20.27.03
Bildschirmfoto 2020-03-05 um 20.27.031476×376 70.7 KB

yes, thanx for taking a look!

18

I wonder if this is a regional thing. Some countries use semicolons instead of commas. I don’t know how that works with the column heading though. Change commas to semicolons and semicolons to commas.

19

What country is your spreadsheet set to? Some countries expect ; (semi colon) separators instead of , (comma) separators. File/Spreadsheet Settings Locale

20

Hi!

Now it works - I had to change “,” to “;” - my locale is set to Germany.

Thx a lot!!
Jan

1 Like