Identify the fortnight to which the row belongs

Good afternoon, I ask you for help to do the following:
I currently have an employee control application with the dates and times of entry and exit. In the database made in Google sheets add a column with this formula =SI(DAY(B2)<=15;" FIRST ";"SECOND “)&“FIFTNIGHT OF “&TEXT(MONTH(B2);”#0” )&” FROM "&TEXT(B2;“YYYY”)
Because I need that column to identify which fortnight of which month that row belongs to. But it doesn’t work in the app.
Can somebody help me? I don’t understand very well the language of Glide

Your formula is wrong… what are you trying to do?
Try this:

In Glide, you can use the math column day(date)… month(date)… year(date)… then apply with IF-ELSE column… then use results in the template column

Also, in Glide, you can use an Excel column… pass there this formula:

IF(DAY(a1)<=15,"FIRST ","SECOND ")&"FIFTNIGHT OF "&MONTH(a1)&" FROM "&year(a1)

Thank you very much for your help. But I think I’m doing something wrong since I apply the excel formula but it doesn’t calculate.

I command example image.

Hello, you could try to perform the computation within Glide Tables. Would that be an option?

What are you try to do: you have a list of dates and for each row you would like an output of 1 if the date is between the 1st and 15th of the month and an output of 2 if the date is between the 16th and end of the month?

Sure, that’s right. I have to do the calculation inside the slip tables.
Now I add an example image made in a spreadsheet so that the format is better understood, this is to be able to filter by fortnight within the application

Here is an example of how it could be done in Glide Tables.


CleanShot 2023-05-05 at 16.30.20


CleanShot 2023-05-05 at 16.30.48


CleanShot 2023-05-05 at 16.31.15

MONTH(Date) with Luxon

CleanShot 2023-05-05 at 16.31.48


CleanShot 2023-05-05 at 16.32.13


CleanShot 2023-05-05 at 16.32.37

NB. I didn’t use the column MONTH(Date) with Math but I could have by creating a relation from that column to a separate table that would map each month to its number and then pulling in the name of the month with a lookup column.

1 Like

I’m perfect!! thank you very much for your help.

1 Like

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.