How can Icalculate number of days worked each week based on data entered in another field?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
I am trying to figure out how to calculate the number of days worked each week based on a another field greater than 0. I have three fields, date field [Prospecting Date], numeric field [# of Real Estate Conversations], and a text formula field [Day of Week]:
Case(DayOfWeek([Prospecting Date]), 0, "Sunday",
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
6, "Saturday")

I want another field that calculates the number of days worked during each week based on if there has been a number entered in the [# of Real Estate Conversations] field. So if I have 7 conversations on a Sunday (11/5/17) and 0 conversations on a Monday (11/6/17), the number of days worked for that week would be 1. Then if I add 3 conversations for Tuesday (11/7/17) the [Number of Days Worked per Week] would be 2, and so forth. Is this possible?
Photo of Dawn Rene

Dawn Rene

  • 526 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,758 Points 20k badge 2x thumb
You would need to summarize qualifying days (days that have a conversion) up to some parent record, maybe a weeks table, or a months table.

Depending on the level of details you need and the historical reporting needed you might need some joined tables.

What are some of the end results you are looking to do with that "# of Days Worked"?
What reports?  What time frame?  Historical needed, or just a rolling total?