# Google sheet functions to manipulate GLide date format

**URL:** <https://community.glideapps.com/t/google-sheet-functions-to-manipulate-glide-date-format/61588>\
**Category:** Ask for Help\
**Created:** [May 10, 2023, 2:46pm UTC](https://community.glideapps.com/t/google-sheet-functions-to-manipulate-glide-date-format/61588 "2023-05-10T14:46:52Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![MattLB](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mattlb/32/41284_2.png) [@MattLB](https://community.glideapps.com/u/MattLB)\
**Post date:** [May 10, 2023, 2:46pm UTC](https://community.glideapps.com/t/google-sheet-functions-to-manipulate-glide-date-format/61588/1 "2023-05-10T14:46:52Z")

</div>

This is what glide stores in a google sheet.

![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/b/3b0f412e0e6787a0894408fe2267bb0c4d84d74b.png)

When I look at the cell it is:

![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/7/476422a875dec5c00c91f141b32ebd5a61e018dc.png)

I am trying to do a vlookup on 5/1/2023 so I need to get from ‘Convert Date’ (the Glide/Sheets date format stored in sheets) to CVT-Date2 and I can not find any formatting/transformation that works simply. The monstrous way I got it to work was transforming the Glide date into text then pulling each compenent out (M-D-Y) then formatting them appropriately and concatenating them back together and finally using datevalue().

Here is the final result which I am trying to get to without using 7 columns in sheets:  
 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/a/4a58df53ac5a500bbc4c2b3d1448d7580e166e31.png)

I then use vlookup in this table:

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/e/8/e8794613d18786c170baf64371093f0193cc4fb8.png)

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [May 10, 2023, 2:58pm UTC](https://community.glideapps.com/t/google-sheet-functions-to-manipulate-glide-date-format/61588/2 "2023-05-10T14:58:49Z")

</div>

Why do you need to do a VLOOKUP in your Google Sheet?

The only reason I could imagine for doing that would be if you’re using the data outside of Glide. Otherwise, just use relations and lookups.

Anyway, assuming that you have a good reason, my suggestion would be to convert all your dates to numbers in YYYYDDMM format. Then you’ll find them a lot easier to work with. You can do that with a single math column:

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

```

Of course, because that’s a computed column, you’ll need an action to write it to a basic column so that it shows up in your Google Sheet.

---

<div class="post-metadata">

**Author:** ![MattLB](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mattlb/32/41284_2.png) [@MattLB](https://community.glideapps.com/u/MattLB)\
**Post date:** [May 10, 2023, 3:11pm UTC](https://community.glideapps.com/t/google-sheet-functions-to-manipulate-glide-date-format/61588/3 "2023-05-10T15:11:27Z")

</div>

The rather poor reason I am doing this is because lookup/compute columns do not work as the source of a query. I can do the same lookup in sheets and it does work in the query since the source is now a sheets field and not a lookup/compute glide column. But getting the date to lookup properly has been a challenge. I found a way to do it but it is ugly.

It is stop-gap until query works with compute/lookup columns.

But working with glide dates in sheets has been…challenging.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [May 10, 2023, 3:22pm UTC](https://community.glideapps.com/t/google-sheet-functions-to-manipulate-glide-date-format/61588/4 "2023-05-10T15:22:16Z")

</div>

Okay. I never really figured out what causes dates coming from Glide to show up as ISO formatted strings. I used to get hung up about it - even to the point of writing scripts to try and convert them back to dates. Now, I just don’t bother, I have sheets that are littered with date/time columns that look like so:

![CleanShot 2023-05-10 at 23.16.36](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/2/1/21381b8887139740d50a9d6774496b8241eb78ce.png)

But I don’t care anymore, because I don’t actually do anything with the data in the Google Sheet. It’s just a repository.

I know I’m not answering your question, but my advice would be don’t bother. It’s a really deep rabbit hole that’s best avoided. Just do whatever you need to do in Glide 🤷‍♂️

---

<div class="post-metadata">

**Author:** ![MattLB](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mattlb/32/41284_2.png) [@MattLB](https://community.glideapps.com/u/MattLB)\
**Post date:** [May 10, 2023, 3:39pm UTC](https://community.glideapps.com/t/google-sheet-functions-to-manipulate-glide-date-format/61588/5 "2023-05-10T15:39:59Z")

</div>

I am a few hours into this rabbit hole and it is a stop-gap. I will use my kludgy method - it works and hopefully temporary. Thanks for confirming this is something to not do in the future.

Last note on this topic. I do have to fill the sheet with the formula. I found this apps script formula - si this a ‘good practice’?

function fillDown() {  
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Example 7”);  
ss.getRange(“B2”).setFormula(“=A2\*0.05”);  
var lastRow = ss.getLastRow();  
var fillDownFormulaRange = ss.getRange(2, 2, lastRow-1);  
ss.getRange(“B2”). copyTo(fillDownFormulaRange);  
}

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [May 10, 2023, 3:48pm UTC](https://community.glideapps.com/t/google-sheet-functions-to-manipulate-glide-date-format/61588/6 "2023-05-10T15:48:29Z")

</div>

The problem with that script is that you’ll need to execute it every time a new row is added to your sheet. You’d be better off with an ARRAYFORMULA.

`=ARRAYFORMULA(A2:A*0.05)` would do the same job as that script, and will automagically be applied to new rows.

Make sure you remove all empty rows from the bottom of the sheet.

---

<div class="post-metadata">

**Author:** ![MattLB](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mattlb/32/41284_2.png) [@MattLB](https://community.glideapps.com/u/MattLB)\
**Post date:** [May 10, 2023, 4:22pm UTC](https://community.glideapps.com/t/google-sheet-functions-to-manipulate-glide-date-format/61588/7 "2023-05-10T16:22:10Z")

</div>

Exiting the rabbit hole. Going with your other suggestion and instead of using the lookup for the query I am doing the lookup in the ‘create’ form and writing that to a new google field. Then query on that.

Much easier on my brain.

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 10, 2023, 11:55pm UTC](https://community.glideapps.com/t/google-sheet-functions-to-manipulate-glide-date-format/61588/8 "2023-05-10T23:55:36Z")

</div>

Not super related, but I use BYROW + LAMBDA a lot these days. It works with functions that don’t work with ARRAYFORMULA.