Relation issue

Basically i have two sheets .
Gazette Holiday and TimesheetDAY where it should be related using the “Date” column.

Image 1 : Showing the Gazzeted Holiday for 18 Nov 2022 (row 41)

Image 2 : The TimesheetDAY sheet which have date 18 Nov 2022
Row 1440

Image 3 : Using Relation Column using “Date” column

Image 4 : Using lookup. Nothing come out.

My question is how to solve ?

are you sure these are the exact dates? Remember dates have time… with seconds and milliseconds… to match just a date, you need to convert it to text

Like , i have created this apps for years ago. I noticed in the mid of July the format i added to the row have been changed

try to convert… and see if it helps… use the format date column… it works for me

but when the new row added, the format is not as above cell right.

just beware, the Format Date plugin is known to give inconsistent results.

It’s much safer to convert the dates to an integer using a math column:

Year(Date) * 10000
+ Month(Date) * 100
+ Day(Date)

and then use that for your relation.


Hi @Darren_Murphy ,
I tried, but the answer is different with the original date
Original date is 5 Nov 2022 (Image 1) after using the math column and add row it become as Image 2.

Image 1

Image 2

Yes, you are right, date format column is not consistent,
Eg: just now i created dd/MM/yyyy format. seems okay.
after some time the result of added row was. MM/dd/yyyy

it is important lower or upper case … and positon… fallow my example

Yes, but at the added row sheet , date(“dd”) at the front ?

or simply do the text date… it will never break… make 3 math columns to separate day… month… year… then use template column to join… it will become solid text… nothing can break that

1 Like

I don’t really understand what you’ve done there. The math column should only be used for creating the relation, it should not be written back into the Google Sheet.

Can you show me the configuration of your Math column?

here you go

Whoops, my bad. The formula I gave you was missing a zero…

Try this:

Year(Date) * 10000
+ Month(Date) * 100
+ Day(Date)

i see we have defernt bases , how to approach data lol… main goal is to get off Glide marking records… is anoing and wrong

Our date format different ?

all over the world … time/date format is always hard to do… I would be happy if Glide follow GS

@biha - I’m not sure if you’re understanding what I’ve suggested. That math formula that I gave you should be created in both tables. Then you create a relation that matches the values in the two math columns.

oh! got it got it . Both.

Well, in the context of this thread…

  • a) I don’t believe that GS formulas are needed, so they are best avoided, and
  • b) I don’t trust the Format Date plugin, so that is also best avoided

That leaves Glide Date Math, which does the job perfectly well :wink: