Pages

Friday, February 21, 2025

CRM Analytics: Calculate aging excluding weekends in Dataflow

We shared the Salesforce formula field to calculate aging without the weekend here. But can we replicate it in CRM Analytics? 

Here is the formula using dataflow in compute expression:

Full_Weeks (in Numeric)
floor(date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch))/7) * 5

Remaining_Days (in Numeric)
date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch)) % 7

Start_Day (in Numeric)
day_in_week(toDate(CreatedDate_sec_epoch))

Weekend_Adjustment (in Numeric)
case when (Start_Day + Remaining_Days) > 7 then 2 
     when (Start_Day + Remaining_Days) == 7 then 1 else 0 end

Business_Days_Aging
Full_Weeks + Remaining_Days - Weekend_Adjustment




Wednesday, February 12, 2025

Convert SOQL result Date/Time field to Excel

Sample: SELECT Id, CreatedDate FROM Account

Result of CreatedDate: 2024-08-14T14:48:05.000+0000

To Convert into Excel as Date/Time format:

  1. Formula =VALUE(SUBSTITUTE(LEFT(A2, 19), "T", " "))
  2. Format Cells... (or Ctrl+1) for the above formula cell, select Custom, and Type: yyyy-mm-dd hh:mm:ss
  3. Done