Amazing, thank you guys. It's now more accurate than the excel calculation. Much appreciated! ... View more

Yes i was a little hasty, i noticed a miscalculation on June 5th but your most recent comment seems to have rectified it. I'm now using - | eval Created2=strptime(Created,"%d/%m/%Y %H:%M") | eval today = now() | eval createdW = strftime(Created2,"%w") | convert num(createdW) | eval createdW = if(createdW == 0, 7, createdW) | eval todayW = strftime(today,"%w") | convert num(todayW) | eval todayW = if(todayW == 0, 7, todayW) | eval totalDays = floor(((today-Created2)/86400)) | eval Aging = totalDays - 2 * floor((totalDays/7)) | eval Aging = if(totalDays % 7 != 0, case(createdW = 7, Aging-1, todayW = 7, Aging-1, todayW < createdW, Aging-2, 1=1, Aging), Aging) Cheers buddy ... View more

Thank you @FrankVl & @VatsalJagani ! This works perfectly when taking @FrankVl last comment into consideration "Try adding a floor to the second to last line: | eval noOfBusinessDays = totalDays - 2 * floor((totalDays/7)) You don't want to take the decimal part of totalDays/7 into account." ... View more

Hi @FrankVl & @VatsalJagani , I think we're close but it's not quite calculating correctly. I've attached a screenshot of my results in excel. I used almost the exact query (I just changed the eval to make a Created2 field so i could retain the Created field). The results for June 4th & 5th are more accurate than the excel answer, taking the time into consideration, however the results for June 6th, 7th & 10th are not accurate. You'll notice a jump in noOfBusinessDays between the 5th & 6th. | eval Created2=strptime(Created,"%d/%m/%Y %H:%M") | eval today = now() | eval createdW = strftime(Created2,"%w") | convert num(createdW) | eval createdW = if(createdW == 0, 7, createdW) | eval todayW = strftime(today,"%w") | convert num(todayW) | eval todayW = if(todayW == 0, 7, todayW) | eval totalDays = round(((today-Created2)/(86400)),2) | eval noOfBusinessDays = totalDays - 2 * (totalDays/7) | eval noOfBusinessDays = if(totalDays % 7 != 0, case(createdW = 7, noOfBusinessDays-1, todayW = 7, noOfBusinessDays-1, todayW < createdW, noOfBusinessDays-2, 1=1, noOfBusinessDays), noOfBusinessDays) Thanks again! Lewis ... View more

Thanks guys, but i should have made it clear that i'm only using a .csv file for this query so i don't have any time fields except for the one i've created below - | eval Created=strptime(Created,"%d/%m/%Y %H:%M") | eval c_time=strftime(Created,"%d/%m/%Y %H:%M") | eval aging=round((now()-strptime(c_time, "%d/%m/%Y %H:%M"))/86400,2) Cheers, Lewis ... View more