Daystart function not working as expected

[#1]

Further to my issue with trying to calculate the time between a lead being received, and it being processed, I had to devise a way to deal with zealous Admins who came in before 9 am to catch up on their work. I thought this would work:

AdjustedLeadDate = if(hour(IncLeadDate)<9,daystart(IncLeadDate,0,.375),IncLeadDate)

This would set all the incoming leads that came in early to 9:00 AM, and those that came in after 9 AM to their actual time.

However, when I put this in my report, I would get the following results:

IncLeadDate            AdjustedLeadDate

3/15/13 8:38 AM      3/14/13 9:00 AM   <-- Note the date is adjusted to previous day, even though shift is zero

So I thought maybe the documentation was incorrect, and I had to put in daystart(IncLeadDate,1,.375). But when I did that,

I got:

IncLeadDate            AdjustedLeadDate

3/15/13 8:38 AM      3/16/13 9:00 AM <-- Note the date is adjusted to next day.

Any thoughts on this?

Kevin

[Gysbert_Wassenaar · Accepted solution]

As I understand it if you set the start of the day to 9:00 AM then times before that day fall in the previous day. The daystart of such datetimes would be 9:00 AM the previous day. That doesn't explain why you get 3/16/9:00 AM if you use an offset of 1 day. I haven't been able to reproduce that here.

Alternatively you could try:

if(hour(IncLeadDate)<9,floor(IncLeadDate) + 0.375,IncLeadDate)

[#3]

Thanks, that worked well. (using floor(IncLeadDate)+.375)

Still don't understand, though - there's nothing in the documentation that says "daystart()" considers times before 9 am to be the previous day, and the function works fine when I'm using it to adjust leads that come in on Saturday or Sunday to calculate as if the lead came in Monday at 9 am.

But problem solved, so thanks again!