Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Makedate function not working

I have a text field with Date in format Mmm dd, yyyy (ex: Jan 8, 2015, Mar 13, 2015)

Qlik wont recognize it as date so trying to use the makedate function below and the year and month work fine but the Day comes through as 1 only.  But when I pull the textbetween function out on its own and run it, I get the actual day number (8, 13).  Why is it putting in a 1 when used in MakeDate function.  I tried to convert to Num and trim spaces but still doesn't work.  It appears it is not recognizing it as a number.  what am I missing and is there a better way to get the Day out rather than using the textbetween.  the number is either 1 or 2 digits so cannot use mid function.

MakeDate(

  right([Timesheet Entry Date],4),

  applymap('Month_Map',left(upper([Timesheet Entry Date]),3),

  num(trim(TextBetween([Timesheet Entry Date],' ',',')))))

1 Solution

Accepted Solutions
MK_QSL
MVP

Use below line in your script while loading the date field

=Date(Date#(YourDateField,'MMM D, YYYY'))

View solution in original post

7 Replies
MK_QSL
MVP

Use below line in your script while loading the date field

=Date(Date#(YourDateField,'MMM D, YYYY'))

Anonymous
Not applicable
Author

I had success with this formula

=

mid('Jan 13, 2015', FindOneOf('Jan 13, 2015',',')-2,2)

it works with 13 and with 8 in a textbox

hope it works for you too

Not applicable
Author

THANK YOU!!!! WORKED PERFECTLY and SO MUCH SIMPLER!!!

Still confused why the above doesn't work because the same exact one works when embedded in the Weekstart() function.  It actually gives me the correct weekstart based on the date, not the first of month week start.  I copy and pasted it from one to the other. 

Anonymous
Not applicable
Author

=Date(Date#('Jun 8, 2015','MMM D, YYYY'))

MK_QSL
MVP

Try this...

Date(MakeDate(

  right([Timesheet Entry Date],4),

  applymap('Month_Map',left(upper([Timesheet Entry Date]),3),

  num(trim(TextBetween([Timesheet Entry Date],' ',','))))))

Let me know the Date Format inside your script..

SET DateFormat=

MK_QSL
MVP

Kindly close the thread if you have received your answer.

Not applicable
Author

Not sure what you mean by "close the thread"  I believe we want others to be able to search and find the answer via this thread.  I marked your answer as correct.