Through copying other formulas/some help on this forum/blagging it I have managed to write a formula for a report I've create,d that basically takes the start date and the duration of the line entry and then returns the number 0.33 in separate month columns that fall between the start date and the end date.
Depending on the category of the line entry I also need some of the entries to return 0.2 for the relevant months instead of 0.33. I've managed to change the relevant parts of the formula to do this so can manually copy that formula to the relevant cells but ideally I would like to combine these both into one formula which can be applied to the whole sheet to make it as automated as possible. I've tried combining these myself but everything I have tried returns an error.
The two different formulas are:
=IF($L2=1,IF(AND(AH$1>=DATE(YEAR($N2),MONTH($N2),1),AH$1<=DATE(YEAR($N2),MONTH($N2)+($O2-1),1)),0.33,""),"")
=IF($L2=3,IF(AND(AH$1>=DATE(YEAR($N2),MONTH($N2),1),AH$1<=DATE(YEAR($N2),MONTH($N2)+($O2-1),1)),0.2,""),"")
Basically if the number in Column L is 1 I want the relevant month columns to return 0.33 and if the number in Column L is 3 I want the relevant month columns to return 0.2. If the number is not 1 or 3 then I want the month cells to be blank.
Please can you help me combine these IF
statements?
Let me know if you need any more information.