Dates and Calendars

[#1]

I appreciate there are many, many discussions on here about this subject, but having spent half a day trawling through them I am still at a loss!

I have two calendar objects, which are linked to variables, and in the Min and Max Value I have entered =min({1} DATE(OCR_DATE_TIME_SENT_TO_OCR, 'DD/MM/YYYY')) and =MAX({1} DATE(OCR_DATE_TIME_SENT_TO_OCR, 'DD/MM/YYYY')) . the number format on both objects is DD/MM/YYYY.

I have formatted the field OCR_DATE_TIME_SENT_TO_OCR in the load script like this:Date(DATE_TIME_SENT_TO_OCR, 'DD/MM/YYYY') as OCR_DATE_TIME_SENT_TO_OCR - this works as when I use this field in a table or simply add it to the sheet, it appears in this format without any need to amend the number forma

have set the Date format as

SET DateFormat='DD/MM/YYYY';

But when I pick from my calendars, nothing is selected. And in the variable overview, the definition of my variables is in this format:

42775.753472222

So I can see that the date formats are not the same, but I cannot see why the variable is still being populated with numbers. I even tried removing formatting from everything, and it still wouldn't select anything!

All help appreciated

Ella

[adamdavi3s]

Hi Ella,

The trick is to floor the date here, its always a pain!

try something like this: DATE(floor(min({1}OCR_DATE_TIME_SENT_TO_OCR)), 'DD/MM/YYYY'))

possibly this, just experiment with it!

MIN(NUM(DATE(FLOOR(Timestamp#(OCR_DATE_TIME_SENT_TO_OCR)),'DD/MM/YYYY')))

[#3]

Sorry, still doesn't select anything, although the format in the variable definition has now changed to be just 42775

[adamdavi3s]

Hi Ella,

Can you upload a sample at all? Sorry hard to picture it without it in front of me

Which formula did you use in the end? if it was the latter then wrapping it in another date might work

DATE(MIN(NUM(DATE(FLOOR(Timestamp#(OCR_DATE_TIME_SENT_TO_OCR)),'DD/MM/YYYY'))),'DD/MM/YYYY')

[#5]

I can't see a link to upload a qv file - is it me?!

[adamdavi3s]

you have to go to 'use advanced editor' then use the attach / paperclip icon down the bottom

[#7]

thanks! Have restricted it down to just a few months to keep it small - hope it helps!

[adamdavi3s]

Perfect

Ok so what is the plan, just get something to show you the dates between your two selections?

[adamdavi3s]

Ok so I tried this formula in a table with the ocr date as a dimension and it worked OK for me

if(OCR_DATE_TIME_SENT_TO_OCR >= $(vStartDate) and OCR_DATE_TIME_SENT_TO_OCR <=$(vEndDate),1,0)

[#10]

That's it - when it works, I would expect the date fields at the top be impacted by the selections, and the selections themselves to appear in the 'Selections' box.