Format issue dates google sheets

[bibbi]

hi

I use the block seconds since 1970 to record the dates or times of different events in a google sheets
arrived in the spreadsheet I can not format these different information
and I don’t see what’s blocking

[tatiang]

It sounds like you are updating cells in Google Sheets with the value from the seconds since 1970 block. Please post a screenshot of what a cell with the date value looks like in Google Sheets. And also the blocks you’re using in Thunkable (if you’re having trouble updating a cell).

How are you trying to format the value in Google Sheets or Thunkable? And what do you want it to look like when it works?

[muneer]

If you are storing the date in Google sheet using the block seconds since 1970 then you can use my solution in this post to turn it to normal date (note the last part of adding 5:30 hours is to adjust it for Indian local time)

Open Weather Map Sunrise/Set Parsing

This one is easy because Thunkable is using seconds since 1970 and what I do is connect a Google sheet as my data source and this sheet has two columns Unix-time and normal-time In the column normal time I have a formula =A2/86400+DATE(1970,1,1)+TIME(5,30,0) Then in Thunkable I update cell A2 and read cell B2 which has my answer. I use it to compute number of dates between two dates and other date functions.

[bibbi]

here is what i get in google sheets

i format data in google sheets
in the date column I would like to get the dates and in the hours column see only the hours

[bibbi]

when i use your formula i get formula parsing error code

[tatiang]

You shouldn’t be getting decimal values. The number of seconds that have elapsed is always going to be an integer.

What blocks are you using to update those cells?

[muneer]

@bibbi

i get formula parsing error code

Can you show the formula you used in the sheet?

@tatiang

You shouldn’t be getting decimal values

This is correct. The block seconds since 1970 gives this output with decimal places.

[muneer]

I just tried the first number using my formula and I can get the date

Using the formula =G2/86400+DATE(1970,1,1)

[tatiang]

@muneer

This is correct. The block seconds since 1970 gives this output with decimal places.

Sorry, my mistake!

[bibbi]

here is what i do :

[tatiang]

I can confirm that @muneer’s formula works. So maybe your A2 cell is not formatted correctly. What is the cell format for A2

[muneer]

Format your date column as a number only. It should work.

[bibbi]

for me your formula must be written like this :
=A2/86400+DATE(1970;1;1)
to work and data like this :
1635081029,95
I don’t understand why the formatting commands built into google sheet don’t work I should probably determine the dates in thunkable and calculate the differences between the times also in thunkable

[muneer]

You are setting your computer to European format which uses the comma instead of the decimal point.