Split date and time

[CreoLTD112]

Good Day,

I have an issue where by my date and time is currently combined into one field  

e.g

01 April 2018, 1:18:55PM

Field name: Time (extract from excel)

i need to split this into two columns eg:

TIme                               Date

1:18:55PM                     01 April 2018

the time field will not be required, however the date will be used in several charts ,

Main Question:

How do i split the date and time?

How can align the dates to only be Jan,Feb,March..... (as right it is Jan,Jan,Jan,Jan,Feb,Feb,Feb,March,March,April....)

Any suggestions would be really appreciated

Thanks

[Colin-Albert]

These blogs should help.

https://community.qlik.com/t5/Qlik-Design-Blog/Get-the-Dates-Right/ba-p/1476178
https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Time-Table/ba-p/1469392
https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

[sunny_talwar]

You can try this

Date(Floor(TimeStamp#(Time, 'DD MMM YYYY, h:mm:ssTT')), 'DD MMM YYYY') as Date,
Time(Frac(TimeStamp#(Time, 'DD MMM YYYY, h:mm:ssTT')), 'h:mm:ssTT') as Time,

[juliensaintmaxent3113]

hello,

to split date and time use the function subfield:

subfield(Yourfield,',',-1) for date

subfield(Yourfield,',',1) for Time

and if you don't want to create master calendar (it's recommended) you can try floor(month(date)), i think it works

Bye 

[hopkinsc]

How about the attached?

[CreoLTD112]

Hi

This could work 

However the only issue is that my date range (subfield Time) is extensively long - e.g: 01 April 2018, 1:18:55PM,01 April 2018, 1:18:55PM,01 April 2018, 1:18:55PM,01 April 2018, 1:18:55PM,01 April 2018, 1:18:55PM, 05 May 2018, 2:45:11,05 May 2018, 2:45:11,05 May 2018, 2:45:11

basically there are over 10000 values within the date range ,is there a method to merge all these values rather than adding them one by one?

[CreoLTD112]

essentially i need the following transaction

chart need to look up the date- and return all values within that month, the months are from Jan to Nov

[hopkinsc]

Hi, do you have an example you could post?