Forum Discussion
Formula help on figuring days remaining
In which cells you need help on the formula? For example, how do you determine the result to be returned by the formula in Sheet1M3?
You stated that:
"What i am needing it to do is, lets say its September 4th, 2018, there are 3 rain days allotted for Sept., then there should be 3 days remaining in Cell M3, but if its the 24th of Sept then there should only be 1 day showing in Cell M3. Or if its the 14th there should only be 2 days showing in Cell M3."
When do those 3 rain days occur in the month of September? Does the formula for Sheet!M3 refer to the month of September only? What about the occurrences of rain days in other months?
I may also add some more questions for you to answer but I believe it is better if you explain your requirement yourself. In explaining, try your very best to consider all possibilities and the result you want for each possibility.
I am needing these formulas to be on the 12 MO. tab in the Gray table. I have formulas set up on this tab to populate the Gray table based on the value of Cell B6, once Cell B6 is populated Cell A19 equals B6, then the rest of the Gray table populates based on the value of Cell A19. The attached workbook shows in Cell A19 5/1/2019, then info. is pulled from Sheet1 for May.
On the Sheet1 tab,in Column E there are set rain days for each month. In May there are 2 historical rain days for the month. 1 rain day would be allowed for the 1st thru the 15th and the second rain day would be for the 16th thru the 31st. 31/2= 15.5 days for each rain day. In January there are 3 historical rain days for that month. 1 day would be allowed for the 1st thru the 10th, the next rain day would be allowed the 11th thru the 20th and the last rain day is from the 21st to the end of the month. So to make it simple for every month take the calendar days divided by Rain days. For January it would be 31/3= 10.33 days for each rain day. In July, there are 4 rain days allowed, so that would be each rain day is 7.75 days. 31/4=7.75. To simplify it just round it to 8 days.
Let me know if you need anything else.
Thanks!Twifoo
- So, the formula you need is limited to rain days only for each of the 3 sheets. Being clearer now, perhaps I can give you the formula tomorrow. I’m just replying to you now via mobile phone.
Hopefully, with your clearer explanation and my understanding of the logic thereof, we will both arrive at an answer acceptable to you.- Ok, Thank you.
Basically, if i can get it to work on the 12 mo. tab i can transfer the formulas to the 24 and 36 mo. tabs.- The formulas in each of the 3 sheets for Columns D to G appear to return the correct results. Please clarify the relationship of those formulas with your statement that:
"The rain days are allocated to each month, such that if there are 2 rain days, 1 rain day is allocated to the 1st to 15th of the month and the other 1 rain day is allocated to the 16th to end of the month."
I see no relationship at all given that all your dates in Column A for each of the 3 sheets are for the 1st day of each month. Thus, there appears no need for the allocation you stated.