Hey, 
I had the timestamp in a metric called “Timestamp”. 
Using the following formula I can extract the time as “14:07”:

LEFT(RIGHT(Timestamp, LEN(Timestamp) - FIND(" ", Timestamp)), 2) // Returns the value 14
& ":" & 
LEFT(RIGHT(RIGHT(Timestamp, LEN(Timestamp) - FIND(" ", Timestamp)),5),2) // Returns the value 07

And yes 14:07 would be 2pm 7minutes.
You can use if formulas to transform the time into that as well by doing something like this:
 

Hope this helps :)

Hi, 

Our AI tool caught something interesting:

(We now run our Pigment AI over each Community Question)

> Regarding the customer's second question, 14:00:00 -07:00 does not mean 2pm 7mins. It means 2pm in a timezone that is 7 hours behind Coordinated Universal Time (UTC).

I’ve checked online and it appears to be right.

2pm7min would have be written this way. 14:07:00 -07:00

Can you double check maybe please? (It’d be great for us to confirm Pigment AI is right  )

In case your model needs to be accurate. 

Best,

Is there a way to convert Text format into “Date” format which will have both date and time ?

Hi Chahat,

Can you share the input and output you’re expecting so we can advise here?

Hi Keiran,
It got resolved through the formulas,
Is there a direct way to fetch the date+time from text format by using a specific ‘FUNCTION’?

There is a customers dimension list with property: Load Date,
Output Required: Get the latest load date / refresh date alongwith time.
‘Load Date’ property in ‘Customers’ dimension list of datatype: ‘Text’ with values like ‘2025-09-09 15:29:47’, etc,

Another ‘loaded date’ property of datatype text : ‘2025-09-09’ (LEFT('Customers'.'Load Date',10))
 

1. Main metric which returns the latest refresh date alongwith time:

LEFT(((DATEVALUE(LEFT('Customers'.'Load Date', 10), "yyyy-MM-dd"))oREMOVE MAX: 'Customers']) & "dt", 10)
// Gives date in text format

&

" (" &
(
'Last Refresh On (HH)'
)
// Returns hour

& ":"&

TEXT(
NUMBER(
MID(
'Customers'.'Load Date'(FILTER:                               
CONTAINS('a. Load Date with Hr',
LEFT(((DATEVALUE(LEFT('Customers'.'Load Date', 10), "yyyy-MM-dd"))oREMOVE MAX: 'Customers']) & "dt", 10) & " " & 'Last Refresh On (HH)'
)]
,15,2)) REMOVE MAX: 'Customers']
)
// Returns minute MM
&

IF(
(NUMBER('Last Refresh On (HH)')) > 12,
" pm PST " ,
" am PST"
)
// Returns am/pm

&
")"

2. ‘Last Refresh On (HH)’ metric :

   IF(
   LEN(TEXT(
   IF(
   (NUMBER(LEFT(RIGHT('Customers'.'Load Date',LEN('Customers'.'Load Date') - FIND(" ",'Customers'.'Load Date')),2))) > 12,
   
   ((NUMBER(LEFT(RIGHT('Customers'.'Load Date',LEN('Customers'.'Load Date') - FIND(" ",'Customers'.'Load Date')),2))) - 12) ,
   
   ((NUMBER(LEFT(RIGHT('Customers'.'Load Date',LEN('Customers'.'Load Date') - FIND(" ",'Customers'.'Load Date')),2))))
   
   )sFILTER: CONTAINS('Customers'.'Loaded Date',
   
   (LEFT(((DATEVALUE(LEFT('Customers'.'Load Date', 10), "yyyy-MM-dd"))sREMOVE MAX: 'Customers']) & "dt", 10) )ABY: ->'Customers']
   )]1REMOVE MAX: 'Customers'])) = 1,
   
   "0" & TEXT(
   IF(
   (NUMBER(LEFT(RIGHT('Customers'.'Load Date',LEN('Customers'.'Load Date') - FIND(" ",'Customers'.'Load Date')),2))) > 12,
   
   ((NUMBER(LEFT(RIGHT('Customers'.'Load Date',LEN('Customers'.'Load Date') - FIND(" ",'Customers'.'Load Date')),2))) - 12) ,
   
   ((NUMBER(LEFT(RIGHT('Customers'.'Load Date',LEN('Customers'.'Load Date') - FIND(" ",'Customers'.'Load Date')),2))))
   
   )sFILTER: CONTAINS('Customers'.'Loaded Date',
   
   (LEFT(((DATEVALUE(LEFT('Customers'.'Load Date', 10), "yyyy-MM-dd"))sREMOVE MAX: 'Customers']) & "dt", 10) )ABY: ->'Customers']
   )]1REMOVE MAX: 'Customers']) ,
   
   
   TEXT(
   IF(
   (NUMBER(LEFT(RIGHT('Customers'.'Load Date',LEN('Customers'.'Load Date') - FIND(" ",'Customers'.'Load Date')),2))) > 12,
   
   ((NUMBER(LEFT(RIGHT('Customers'.'Load Date',LEN('Customers'.'Load Date') - FIND(" ",'Customers'.'Load Date')),2))) - 12) ,
   
   ((NUMBER(LEFT(RIGHT('Customers'.'Load Date',LEN('Customers'.'Load Date') - FIND(" ",'Customers'.'Load Date')),2))))
   
   )sFILTER: CONTAINS('Customers'.'Loaded Date',
   
   (LEFT(((DATEVALUE(LEFT('Customers'.'Load Date', 10), "yyyy-MM-dd"))sREMOVE MAX: 'Customers']) & "dt", 10) )ABY: ->'Customers']
   )]1REMOVE MAX: 'Customers']) )

   3. ‘a. Load Date with Hr’ metric​

      LEFT('Customers'.'Load Date',13)
      

      ​​​​​