May 28 2019 05:50 AM
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?
May 29 2019 03:03 AM
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.
May 29 2019 04:42 AM
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
May 29 2019 05:52 AM
M3==DIAS.LAB.INTL('12 MO. Road'!A19;'12 MO. Road'!A19+'12 MO. Road'!B19;;$E$3:$E$14)
May 29 2019 06:33 AM
May 29 2019 08:23 AM
May 29 2019 08:48 AM
May 29 2019 09:49 PM
May 30 2019 04:50 AM
May 31 2019 11:58 AM