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
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
eg:
Original date is 5 Nov 2022 (Image 1) after using the math column and add row it become as Image 2.
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
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?
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…
That leaves Glide Date Math, which does the job perfectly well 