topic Format in QlikView

Format

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have date field am using like this now

Timestamp(Max(RecordModifiedDate),'MMM DD YYYY hh:mm:ss')

result am getting Apr 21 2018 19:56:28

so I want to be displayed like below format

21-Apr-18 4:47 PM EST

how to achieve this?

Re: Format

sunny_talwar — Mon, 23 Apr 2018 11:49:10 GMT

May be this

Timestamp(Max(RecordModifiedDate),'DD-MMM-YY h:mm TT') & ' EST'

Re: Format

vishweshwarisun — Mon, 23 Apr 2018 11:53:06 GMT

Try this

Timestamp(Max(RecordModifiedDate),'DD-MMM-YY hh:mm TT')&'EST'

Re: Format

soniasweety — Mon, 23 Apr 2018 11:54:58 GMT

thanks sunny,

my timestamp is in IST but I need to convert this to EST your expression will not work for me I think

Re: Format

sunny_talwar — Mon, 23 Apr 2018 11:57:01 GMT

Use ConvertToLocalTime() function

Re: Time Zone Conversation

Re: Format

vishalarote — Mon, 23 Apr 2018 12:14:07 GMT

Timestamp(Timestamp#(Max(RecordModifiedDate),'MMM DD YYYY hh:mm:ss'),'DD-MMM-YY h:mm TT') &'EST'

Re: Format

soniasweety — Mon, 23 Apr 2018 12:30:41 GMT

no I need to convert the ist to est

it means now 6:00 pm ist means I need to show 8:30 Am

https://www.worldtimebuddy.com/ist-to-est-converter

Re: Format

soniasweety — Mon, 23 Apr 2018 12:35:14 GMT

youssef belloum

Re: Format

vishalarote — Mon, 23 Apr 2018 12:36:16 GMT

Ok Try this

t1:

load

Timestamp(Timestamp#(Date,'MMM DD YYYY hh:mm:ss'),'DD-MMM-YY h:mm TT') &' '&'EST' as NewDate

inline [

Date

Apr 21 2018 19:24:56

Mar 21 2018 14:23:45

];

Re: Format

vishalarote — Mon, 23 Apr 2018 12:40:29 GMT

if you want max also then i am trying this in front end

=Timestamp(Max(Timestamp(Timestamp#(Date,'MMM DD YYYY hh:mm:ss'),'DD-MMM-YY h:mm TT')),'DD-MMM-YY h:mm TT')

Re: Format

soniasweety — Mon, 23 Apr 2018 12:46:21 GMT

No vishal

I want to convert local time to USA EST tme zone

for your code it will add the est at end of timestamp only

Re: Format

YoussefBelloum — Mon, 23 Apr 2018 12:48:30 GMT

take a look at Michael solomovich on this thread:

Convert local time to GMT

Re: Format

soniasweety — Mon, 23 Apr 2018 12:55:39 GMT

am able to get now but timestamp format I m unable to achieve how to do

ConvertToLocalTime(timestamp(max(RecordModifiedDate),'DD-MMM-YY h:mm TT'),'GMT-09:30')&' EST'

this format not working 'DD-MMM-YY h:mm TT'

Re: Format

vishalarote — Mon, 23 Apr 2018 13:00:58 GMT

Try this it will work

=Timestamp(ConvertToLocalTime(Max(Timestamp(Timestamp#(Date,'MMM DD YYYY hh:mm:ss'),'DD-MMM-YY h:mm TT'))),'DD-MMM-YY h:mm TT')

Re: Format

YoussefBelloum — Mon, 23 Apr 2018 13:05:22 GMT

are trying to convert your local time into EST time or you're trying to convert EST time to your local indian time ??

1. convert UTC/GMT to localtime:

converttolocaltime() like Sunny suggested on the above thread attached

2. convert localtime to GMT:

GMT() function like describe on the thread that I attached above.

Re: Format

soniasweety — Mon, 23 Apr 2018 13:09:25 GMT

nice its working in local need to chk

but one thing am getting result without space between AMEST but I need AM EST for space which chr() function I need to use?

Re: Format

sasiparupudi1 — Mon, 23 Apr 2018 13:17:36 GMT

May be try

=Timestamp(ConvertToLocalTime(Timestamp#('Apr 21 2018 19:56:28','MMM DD YYYY hh:mm:ss'),'Eastern Time (US & Canada)'),'DD-MMM-YY h:mm TT') & ' EST'

Re: Format

vishalarote — Mon, 23 Apr 2018 13:30:52 GMT

Use &' '&expression

Make space in beetween single quotes

Re: Format

soniasweety — Tue, 24 Apr 2018 06:27:51 GMT

but as per this date the output should be 10:00 AMEST but am getting the result 3:56 PM EST

Vishal Arote

how to get the exact ?

this used =Timestamp(ConvertToLocalTime(Timestamp#('Apr 21 2018 19:56:28','MMM DD YYYY hh:mm:ss'),'Eastern Time (US & Canada)'),'DD-MMM-YY h:mm TT') & ' EST'