how to create a formula to capture some future data that is not part of formula

[MikeFrancis1959]

I have a report downloaded from our server showing material being to me with a date and part of my workflow is to convert  the date to a wk number and then it adds up to qty for all weeks. The problem is that if we get material sent to us in a week that is not part of the formula that adds it all together it does not know to add it. Example is that this week I have material that will be here in wk 52 but my formula is only counting wk 45-51 and I have to manually go into the formula to add wk 52, is there an better way to handle this scenario?

Thanks,

Mike

[cjaneczko]

Are you able to add a reference table that contains all 52 or 53 weeks depending on the year? Or can you use a DateTimeDiff?

[StevenP]

@MikeFrancis1959 Can you provide an example of the current workflow you have built out with dummy data?

[MikeFrancis1959]

 I have not forgotten this request, was on PTO a few days and will be back tomorrow and I can provide a sample workflow.

Mike

[MikeFrancis1959]

Here is a copy of what I have and only when I get a notice of a new shipment with a date it creates a week but then I have to look at the data each week to manually adjust. I want to use the date text input file to be used to show all the weeks ( I can trim down file to just show 2023 & 2024) and then the qty fall into the right week, does this make sense?

Mike

[CoG]

Can you put the Summarize tool after you week calculation and Group By Week#?