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:
=IF(DAY(B2)<=15,"FIRST ","SECOND ")&"FIFTNIGHT OF "&MONTH(B2)&" FROM "&TEXT(B2,"YYYY")

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
EXAMPLE 2

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

DAY(Date)

CleanShot 2023-05-05 at 16.30.20

DAY(LastDay(month))

CleanShot 2023-05-05 at 16.30.48

Fortnight

CleanShot 2023-05-05 at 16.31.15

MONTH(Date) with Luxon

CleanShot 2023-05-05 at 16.31.48

Year(Date)

CleanShot 2023-05-05 at 16.32.13

FortnightDisplay

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.