Hi 
I’m using Relative Time column (as prototype, it does’nt matter yet if different Time Zones), and as the text is very simple, I was wondering if I can translate the English text into French with Experimental Code Column, I assume?
Or do you have sth better like Google Translation?
Reminder: the kind of text to translate looks like “5 minutes ago”,“8 days ago”, etc.
Thks
1 Like
You could build a template column that replaces all English words with French words. You’ll just need a replacement for all possibilities.
4 Likes
Hum, so
“n month.s, day.s, hour.s, minute.s ago” & “just now” by
“il y a n mois, jour.s, heure.s, minutes” & “à l’instant”
Sounds doable.
I don’t need screenshots 
1 Like
@L.M I’ve been working on exactly what you want on and off for a few weeks now. I’m sure the code could use some refactoring and I need to do a bit of work on confirming that the rounding up functionality is working, but besides that, it works fine.
If you read the comments at the top you should be able to figure out what all the parameters are about. I’m asking the Glide Team to give us more than 3 parameters inputs for the Code/Javascript column type but until then you can pass the parameters you want in the calling function. I suggest you change the bottom return function and hard code parameters 4 - 9 to suit your needs. There is an example of what they could be for Spanish that is commented out.
Please experiment with it and give me feedback.
/*
Returns a string representing the time difference between two dates rounded to the unit precision.
Test here: onecompiler.com/javascript or jsfiddle.net/
- Parameters:
- **start**, **end** - Both are required and need to be properly formated, as DateTime, strings (i.e. "2021-10-20T14:35:46.217Z") which is how Glide passes them anyway.
- **utc** (optional) - a boolean true use utc instead of local time defaults to false
- **smallestUnitReturned** (optional) - the number of units to return rounded up. Default is 3.
- **maximumUnitsReturned** (optional) - the smallest unit to return rounded up. Default is 5 (minutes).
- **pUnits** (optional) - an array of stings that represent the plural unit words.
The order needs to be: "year,months,days,hours,minutes,seconds,milliseconds"
- **sUnits** (optional) - as above but singular of units. Note array length and
order should be the same as pUnits.
- **separator** (optional) - separator string of units in the returned string. Default is ', '
- **otherFills** (optional) - array that holds the final suffix.
Default is "ago,until,less than"
*/
const luxon = await import("https://cdn.skypack.dev/luxon")
function duration(
start,
end,
utc,
smallestUnitReturned,
maximumUnitsReturned,
unitsPlural,
unitsSingular,
separator,
otherFills
) {
//let luxon = require("luxon");
let date1, date2;
let unitsP = unitsPlural.split(",");
let unitsS = unitsSingular.split(",");
let arrayAll = [];
let roundUp = [];
let arrayOutput = [];
let output = "";
let otherFillsIndex;
let arrayOtherFills = otherFills.split(",");
let count = 0;
let diff;
// check to see if the unit input strings are 7 in length
if (unitsP.length !== 7 || unitsS.length !== 7) {
return "The units string has the wrong number of elements. It must have 7 in each.";
}
if (utc) {
date1 = luxon.DateTime.fromISO(start, { zone: "utc" });
date2 = luxon.DateTime.fromISO(end, { zone: "utc" });
} else {
date1 = luxon.DateTime.fromISO(start);
date2 = luxon.DateTime.fromISO(end);
}
otherFillsIndex = date1 > date2 ? 0 : 1;
if (otherFillsIndex === 0) {
diff = date1.diff(date2, ["years", "months", "days", "hours", "minutes", "seconds", "milliseconds"]);
} else {
diff = date2.diff(date1, ["years", "months", "days", "hours", "minutes", "seconds", "milliseconds"]);
}
arrayAll = [diff.years, diff.months, diff.days, diff.hours, diff.minutes, diff.seconds, diff.milliseconds];
roundUp = [diff.years, diff.months, diff.days, diff.hours, diff.minutes, diff.seconds, diff.milliseconds];
// round up all of the units
if (roundUp[6] > 499) {
// milliseconds
roundUp[5] += 1;
if (roundUp[5] > 59) {
roundUp[4] += 1;
roundUp[5] -= 60;
}
}
if (roundUp[5] > 29) {
// seconds
roundUp[4] += 1;
if (roundUp[4] > 59) {
roundUp[3] += 1;
roundUp[4] -= 60;
}
}
if (roundUp[4] > 29) {
// minutes
roundUp[3] += 1;
if (roundUp[3] > 23) {
roundUp[2] += 1;
roundUp[3] -= 24;
}
}
if (roundUp[3] > 11) {
// hours
roundUp[2] += 1;
if (roundUp[2] > 29) {
roundUp[1] += 1;
roundUp[2] -= 30;
}
}
if (roundUp[2] > 14) {
// days
roundUp[1] += 1;
if (roundUp[1] > 11) {
roundUp[0] += 1;
roundUp[1] -= 12;
}
}
if (roundUp[1] > 5) {
// months
roundUp[0] += 1;
}
// scan the results array
//for (var i = 0; i < arrayAll.length; i++) {
for (var i = 0; i < smallestUnitReturned; i++) {
if (arrayAll[i] > 0) {
count++;
if (count === maximumUnitsReturned) {
if (arrayAll[i] > 1) {
arrayOutput.push([unitsP[i], roundUp[i]]);
} else {
arrayOutput.push([unitsS[i], roundUp[i]]);
}
break;
} else {
if (arrayAll[i] > 1) {
arrayOutput.push([unitsP[i], count < arrayAll.length ? arrayAll[i] : roundUp[i]]);
} else {
arrayOutput.push([unitsS[i], count < arrayAll.length ? arrayAll[i] : roundUp[i]]);
}
}
}
}
for (var i = 0; i < arrayOutput.length; i++) {
output +=
arrayOutput[i][1] +
" " +
arrayOutput[i][0] +
(i + 1 < arrayOutput.length ? separator : " " + arrayOtherFills[otherFillsIndex]);
}
// if the output is blank at this point then the amount of time
// is less than the smallest unit desired
if (output === "") {
output = arrayOtherFills[2] + " 1 " + unitsS[Number(smallestUnitReturned) - 1];
}
return output;
}
function dateDiffDefaults(
start,
end,
utc,
smallestUnitReturned,
maximumUnitsReturned,
unitsPlural,
unitsSingular,
separator,
otherFills
) {
if (start === undefined || end === undefined) {
return "";
}
if (utc === undefined) {
utc = false;
}
if (smallestUnitReturned === undefined) {
smallestUnitReturned = 5;
}
if (smallestUnitReturned > 7) {
smallestUnitReturned = 7;
}
if (maximumUnitsReturned === undefined) {
maximumUnitsReturned = 5;
}
if (maximumUnitsReturned > 7) {
maximumUnitsReturned = 7;
}
if (unitsPlural === undefined) {
unitsPlural = "years,months,days,hours,minutes,seconds,milliseconds";
}
if (unitsSingular === undefined) {
unitsSingular = "year,month,day,hour,minute,second,millisecond";
}
if (separator === undefined) {
separator = ", ";
}
if (otherFills === undefined) {
otherFills = "ago,until,less than";
}
return duration(
start,
end,
utc,
smallestUnitReturned,
maximumUnitsReturned,
unitsPlural,
unitsSingular,
separator,
otherFills
);
}
return dateDiffDefaults(p1, p2, p3)
// Spanish
// return dateDiffDefaults(p1, p2, p3,4,5,"años, meses, días, horas, minutos, segundos, millisegundos","año, mes, día, hora,minuto, segundo, millisegundo"," ","atrás, hasta que, menos que")
5 Likes
FYI, the UTC parameter gives you the accurate time difference in cases where the timeframe spans a daylight savings transition date. I doubt that would ever be needed but I spend hours trying to “fix” the code when I was working on it over the weekend we switched here in the US.
2 Likes
Thks, George 
I’ll test that asap. I have a exam coming soon. Sorry if I can’t reply quickly. I’d love to.
So grateful you guys engage like that. I feel privileged to participate to this incremental process at my tiny level.
1 Like
Hi,
I do this exactly but you need 2 things
- Calculate the time in the correct time zone
- Google translate API key.
-
get users time zone offset: JS column:
return (new Date().getTimezoneOffset())/60.
like @George-Glide showed
-
Calculate the datetime with the offset using a Math column:
now + (Offset/24)
@Darren_Murphy
-
Write this
date at every form submitting as well as the regular “now” date -
Get the relative time using this calculated date
-
Use a YC to get the translation :
https://glide-translate-column-Modif.manun2.repl.co by @Manu.n
You will need an API key from google…
Google Cloud Platform
4 Likes
7 Likes
Thks so much, Lin I’m having so much trouble not getting user’s TZ. Looks like you’re solving several pbs in one shot. I’ll test asap.
3 Likes
yes, I’ve also noticed his absence. He is missed, I hope he is fine and we see him again soon
5 Likes
2 Likes
Hi George, my Date column looks like this (European Format DD/MM/YYYY, HH:MM:SS). So it does not match with the format your JS requires (If I somehow understand the JS). How can I do?
Thks
Hi, Lin.
You can only catch user’s TZ before setting the date in column value, right?
Once it’s stored in the column, it’s too late, or does Glide happen to “internally store the original time zone” (I’m daydreaming).
Thks.
And my dates look like this DD/MM/YY, HH:MM:SS
Did you try it using that Date column as the p1 parameter?
Yes, but it returned nothing. Which I found normal, as long as all my dates are not the Glide format. I don’t know exactly yet what the P1, P2, P3 should be in that order.
I just froze when I noticed the expecting format.
You would pick the start date to put in p1 and the end date to put in p2, you should be able to leave the p3 empty if you want. You do need a start and end otherwise you will see a blank.
1 Like
ok, that’s what I’ve guessed. I’ll try again tomorrow. It’s 21:45 now in Paris and my computer is shut.
I’ll be back. Thks for your patience and assistance, George.
Good Day 
Take a look at this recording.
4 Likes
You could get the date but only in a math column.
The Relative Time column excepts only DateTime columns so you will need to write the date with the offset each time.
Sorry, George. Still no luck. And I’ve been trying since this morning. I really need to do my fitness now I’m soooo late!
The pb is that I can’t be sure of the date format to enter. Here’s what I want with the Relative Time. The actual column type (with a wrong Relative Time : 5 months instead of a few days ago). Then your JS.
My use case will be to display the Relative Time when I last updated the app. So it will always be relative to Now (Time Zone into account, if possible)
I also tried this, with JS dates, and a fixed date in p1 with no success.
I’m confused. I’ll come back after my workout and some breakfast to clear my mind.
Sorry if it seems obvious. But I can’t figure out why the logic is different and there’s always somewhere where the only format is MM/DD/YYYY and not DD/MM/YYYY I just guessed it myself
And if I try this with the standard Relative Time and another Time Format, it returns nothing. So I believe the issue is the same with your JS.
Same result Date or Date 2 (the format that yet works in another table above
but “samedi 20 novembre 2021, 8:00:00” is a fixed date I typed in the cell. As I would to declare whenever I update my app for users to know the Last Update Time.As long as I can’t make it work accurately in English. I’m not in the position to convert, translate anything yet.
Thks