Bubu, after very brief testing, I'm not positive that Jet Essentials is successfully converting the result of this code:
TEXT($E13,"MM/DD/YYYY")&" 01:30:00 PM
into a Date Value.
Try encapsulating ALL of that with a Value() function.
Value(TEXT($E13,"MM/DD/YYYY")&" 01:30:00 PM)
This will convert your text string into an Excel serial number Date value.
Date
Votes
3 comments
-
Jet Reports Historic Posts Official comment I have already got the correct result after asking to jet report support :mrgreen:
my bad, I should use both fields to filter the data :oops: -
Jet Reports Historic Posts Dear All
I'm stuck with this report , I hope someone could help me or give me some suggestions.
I retrieve the data from NAV 2013 R2 (table sales header). I did modify by adding 2 fields (check in and check out).
the Report shown the no of check in for specific time during the check in and check out time.
for example if I have some invoice:
invoice#1 = check in 12PM Checkout 4 PM
invoice#2 = check in 12PM Checkout 4 PM
invoice#3 = check in 12PM Checkout 2 PM
invoice#4 = check in 01PM Checkout 2 PM
invoice#5 = check in 02PM Checkout 4 PM
and in my report layout there are some columns for specific time, E.g 11.30 ; 12.30; 13.30,14.30…01.30 (the next day)
in the column "13:30" if i use formula: =NL("CountUnique","Sales Invoice Header","No.","Checkin",TEXT($E13,"MM/DD/YYYY")&" 01:30:00 PM.."&TEXT($E13,"MM/DD/YYYY")&" 02:29:00 PM") , the result is only "1" (it is because just referring the check in field from invoice #5).
My expected result is "5" because from all invoice, there is occupation for room at 1:30 PM. How to set the filter? please help
For detail illustration you can see it from attached file, I mark it in green highlight for my expected result, and red highlight is the current result from my formula
Many thanks :) -
Jet Reports Historic Posts Hi Jae
Thank you for the reply. What version did you use?
at first when i use the formula, it didn't show the result, but after I install version 2015 it works :)
A new build of Jet Essentials 2015 has been released to the Jet Reports download site: http://jetreports.com/download
This build contains minor updates and bug fixes: http://kb.jetreports.com/article/AA-00981
After installed it, it solved my problem about filtering date and time.
Right now I have other report that need filtering date and time , but it is between 2 fields, and I don't know how to set the formula :(
hmmm, Am I post in the right place? because I just realize it's for jet enterprise, whereas I use jet Essential :mrgreen:
Sorry If I'm wrong :mrgreen:
Many thanks
Please sign in to leave a comment.