Skip to main content
Question

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

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?
    This topic has been closed for replies.

    • MCFNeil
    • Registered
    • November 8, 2017
    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?
    Terms & ConditionsAccessibility statement