Forum Discussion
Formula help on figuring days remaining
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
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.- On the 12, 24, & 36 Mo. Tabs in the gray table, that information is pulled from Sheet1 depending on what month is populated in Cell A19. Then Cells A20 thru to the last month on the Gray Table, then columns B thru H populate accordingly. If the date in Cell A19 is 5/30/2019 the cells B19:G19 will remain the same, resulting in Cell C19 meaning that there is still 9 weekend days remaining for the month, Cell D19 meaning there are 2 rain days remaining for the month, etc. I have the formula to calculate the weekend days remaining on sheet 1 in Cell L3 . I just haven't transferred the formula yet.
"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." For this here, based on the month that is populated in column A (it doesn't matter what year) column D will populate the Rain Days allowed for the entire month pulling that information from Sheet1. In Column A the first and last months populated will not always be on the first of each month. My logical reasoning for 1 day be allowed for the first thru the 15th and the 2nd day be for the 16th thru the end of the month is just splitting the month in half. Or, if it has 3 days allowed split the month into 3rd's, just for simplistic reasons.