count 3 days work per week with 2 different conditions

Options

Hi, I am stuck with this problem 😓 for my food delivery company;

I need to count Sunday, Tuesday and Thursday with 2 different conditions (i.e., these are the delivery days)

Condition 1 = if 5 days per week delivery of food, Thursday should be counted as 1

Condition 2 = if 6 days per week delivery of food = Thursday should be counted as 2



Another accepted solution😊:

Condition 1 = if 5 days per week delivery of food, Thursday should be counted as 0.5

Condition 2 = if 6 days per week delivery of food = Thursday should be counted as 1

Answers

  • Alex.Dixon
    Options

    I need a little more context for the structure of the sheet. Are the individual delivery days their own columns, rows, cells? This sounds like it can be accomplished with some logic functions, but I need more clarity as to the structure of the problem before I can develop a solution. A screenshot of the sheet would be most helpful.

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Options

    Just to understand the first line, are you saying that "Remaining Days" and "Number of Deliveries" are manually entered by the user?

  • Sakantamany
    Options

    These 2 criteria are not crucial however they are appreciated if possible.

    Remaining days should count down automatically till it reaches "0", I guess depending on the Today function, yes "to be entered by user"

    Number of deliveries either 6 or 12 "can be manual or automatic" (10 and 20 days provide 6 deliveries // 12 and 24 days provide 12 deliveries due to the fact that Thursday provide 2 deliveries in this case)

    P.S.

    Thank you Sameer for trying to support even if not possible still appreciate your efforts

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Sakantamany

    I think you need to pick which variable is going to be manually entered, because right now you're asking for Ending Date, days remaining, and number of deliveries to be all be automatically calculated, but they're all dependent on each other.

    So what data do you know from the start of the process?

    Starting date - yes.

    Number of days per week - yes.

    Unfortunately, with just those two known values, you can't calculate Ending Date, days remaining, or number of deliveries.

    If you knew the ending date, you could count the number of weeks, and using that plus the number of days per week, determine the number of deliveries. You could also determine the days remaining.

    If you knew the number of deliveries, you could calculate the ending date, and then using the ending date, calculate the days remaining.

    If you knew the days remaining, you could calculate the ending date and then count weeks and add up number of deliveries.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Sakantamany
    Options

    @Jeff Reisman

    many thanks for your support.

    number of deliveries is known and can be entered manually, its either 6 or 12 deliveries.

    number of days as well is known and can be entered manually, 10,12,20,24,60,72,144.

    "If you knew the number of deliveries, you could calculate the ending date, and then using the ending date, calculate the days remaining."

    I have this data, I don't know how to find the end date.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Sakantamany

    Is "Remaining Days" supposed the number of calendar days before the end date, or is it the number of delivery days left in this period, or is it the number of deliveries remaining?

    Number of Deliveries = 6 or 12 deliveries total? Basically 3 days or 6 days of deliveries for the 6 deliveries per week people?

    I just don't see how these numbers work together:

    If they get 6 days worth of deliveries per week, with a total of 12 deliveries, how are there 24 remaining days? The logic isn't working here.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!