Dynamically adjusting time for unix conversion

[tourless]

Hi Folks.

I'm working with a YMSQL db that stores date/time in unix format. I can use a simple formula to bounce back and forth from one to the other but I'd like to be able to have my sheet update the date for TODAY() and I'd prefer to keep it at the sheet level and not have to rely on scheduling a macro. Any ideas? The goal i to have the date updated automatically and be fed to a query that will only pull data for the date given. Otherwise I'm looking at a huge table with upwards of 400K rows.

BI Dashboard Test v1.xlsx
	A	B
1	1682683200	4/28/23 12:00 PM
2
3	4/28/2023	1682683200
4
5	45046 12:00 PM	#VALUE!
Parameters

Cell Formulas
Range	Formula
B1	B1	=(((A1/60)/60)/24)+DATE(1970,1,1)
B3,B5	B3	=(A3-DATE(1970,1,1))*86400
A5	A5	=TODAY() & " 12:00 PM"

 

[DanteAmor]

Hi @tourless. Thanks for posting on the board.

Review the formula in A5:

=TODAY() + "12:00 PM"

Dante Amor
	A	B
1	1682683200	28/04/2023 12:00 p. m.
2
3	45044.5	1682683200
4
5	30/04/2023 12:00 p. m.	1682856000
Hoja3

Cell Formulas
Range	Formula
B1	B1	=(((A1/60)/60)/24)+DATE(1970,1,1)
B3,B5	B3	=(A3-DATE(1970,1,1))*86400
A5	A5	=TODAY() + "12:00 PM"

I hope it helps you with your process.
With pleasure.
Dante Amor
----------------

 

[tourless]

Hi @tourless. Thanks for posting on the board.

Review the formula in A5:

=TODAY() + "12:00 PM"

Dante Amor
	A	B
1	1682683200	28/04/2023 12:00 p. m.
2
3	45044.5	1682683200
4
5	30/04/2023 12:00 p. m.	1682856000
Hoja3

Cell Formulas
Range	Formula
B1	B1	=(((A1/60)/60)/24)+DATE(1970,1,1)
B3,B5	B3	=(A3-DATE(1970,1,1))*86400
A5	A5	=TODAY() + "12:00 PM"

I hope it helps you with your process.
With pleasure.
Dante Amor
----------------

Good stuff Dante, thank you.