Formula help on figuring days remaining

Copper Contributor

In the attached workbook on SHEET1 in Cell M3, i am trying to get it to calculate rain days based on the corresponding month. Once i get this figured out i will transfer the formula to 12 MO. Road tab, 24 Mo. Road tab and the 36 MO. Road tab.

 

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.

 

Also, in Cell P3 is there a way to figure if the holiday has already passed?  Lets say its January 9th, 2019 and there is 2 holidays allowed for January. One of them is observed on the 1st and the other one is observed on the 3rd Monday of the month. Is there a way to calculate Holidays remaining for the month based on the date?

 

Overall, what i'm trying to do is, on the 12, 24 & the 36 Mo. tabs is to enter in dates in Cells B5 & B6 and everything in the Gray table automatically calculates everything else.

 

Is any of this possible?

9 Replies

@drt_80 

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 

@drt_80 

M3==DIAS.LAB.INTL('12 MO. Road'!A19;'12 MO. Road'!A19+'12 MO. Road'!B19;;$E$3:$E$14)

I get an error of to many arguments for this function.
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.
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.
I regret to inform you that, based on your apparently painstaking explanation, I unfortunately found no cogent reason to suggest any solution to an issue which, for me as of now, is impliedly inexistent.
If you can attach a sample file with the expected results for each possibility you claim to exist, I can perhaps give you an objective solution.