Skip to main content
Solved

Extract time from Text format

  • October 18, 2024
  • 5 replies
  • 164 views
Chahat
Master Helper
Forum|alt.badge.img+14

Example:
“10/17/2024 14:00:00 -07:00” is a text format; Date with time.
I need to extract time from this text format , how to do in Pigment?
Also, does it mean 2pm 7mins?

Convert text to date&time format

Best answer by Lehtohen

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 :)

5 replies

Lehtohen
Master Trendsetter
Forum|alt.badge.img+2
  • LehtohenMaster Trendsetter
  • Master Trendsetter
  • 16 replies
  • Answer
  • October 23, 2024

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 :)

Lehtohen
Benoit
Community Manager
Forum|alt.badge.img+15
  • BenoitCommunity Manager
  • Community Manager
  • 232 replies
  • October 29, 2024

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,

Benoit - Pigment wizard
Chahat
Master Helper
Forum|alt.badge.img+14
  • ChahatMaster Helper
  • Author
  • Master Helper
  • 79 replies
  • September 8, 2025

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

Keiran
Employee
Forum|alt.badge.img+11
  • KeiranEmployee
  • Employee
  • 68 replies
  • September 8, 2025

Hi Chahat,

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

Keiran | Pigment Specialist (Head of Support, EMEA)
Chahat
Master Helper
Forum|alt.badge.img+14
  • ChahatMaster Helper
  • Author
  • Master Helper
  • 79 replies
  • September 10, 2025

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"))[REMOVE 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"))[REMOVE 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

&
")"

 

  1. ‘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))))

    )[FILTER: CONTAINS('Customers'.'Loaded Date',

    (LEFT(((DATEVALUE(LEFT('Customers'.'Load Date', 10), "yyyy-MM-dd"))[REMOVE MAX: 'Customers']) & "dt", 10) )[BY: ->'Customers']
    )][REMOVE 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))))

    )[FILTER: CONTAINS('Customers'.'Loaded Date',

    (LEFT(((DATEVALUE(LEFT('Customers'.'Load Date', 10), "yyyy-MM-dd"))[REMOVE MAX: 'Customers']) & "dt", 10) )[BY: ->'Customers']
    )][REMOVE 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))))

    )[FILTER: CONTAINS('Customers'.'Loaded Date',

    (LEFT(((DATEVALUE(LEFT('Customers'.'Load Date', 10), "yyyy-MM-dd"))[REMOVE MAX: 'Customers']) & "dt", 10) )[BY: ->'Customers']
    )][REMOVE MAX: 'Customers']) )
    1. ‘a. Load Date with Hr’ metric​ 
      LEFT('Customers'.'Load Date',13)
      ​​​​​


Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement