Hi,
I'm trying to make what I'm sure ought to be a simple calendar to track two types of work arriving and the time that they must be dealt with in.
Work type 1 (addressed) has a receipt day and then three days to be dealt with (labeled day 1, day 2, etc). Work type two (unaddressed) has a receipt day and severn days to be dealt with (again labeled day 1, day 2, etc). Only working days count so where a Saturday or Sunday are in between two work days I just have them labeled "Saturday" and "Sunday". The user will enter a date in a collum on the left (for receipt date in collum C) and then a work type in colum B. There is only ever one period of work per row and it is always either 3 or 7 working days after the recipt day. Cells without work days should be blank.
I have the Saturday and Sunday set up quite happily but am struggling with the Monday to Friday formula which is a monster:
=IF($C3=E1,CONCATENATE("Receive ",$B3),IF(OR(D3="Receive Addressed",D3="Receive Unaddressed"),"Day 1",IF(D3="Day 1","Day 2",IF(D3="Day 2","Day 3",IF(D3="Sunday",IF(OR(B3="Receive Addressed",B3="Receive Unaddressed"),"Day 1",IF(B3="Day 1","Day 2",IF(B3="Day 2","Day 3",IF($B3="Unaddressed",IF(D3="Day 3","Day 4",IF(D3="Day 4","Day 5",IF(D3="Day 5","Day 6",IF(D3="Day 6","Day 7",IF(D3="Sunday",IF(B3="Day 3","Day 4",IF(B3="Day 4","Day 5",IF(B3="Day 5","Day 6",IF(B3="Day 6","Day 7",""))))))))))))))))))
This goes in E4 (the first Monday) and then I would copy paste it over the rest of the cells. There seem to be two problems. The cells that are not work days are showing "False" rather than being blank, and, more problematicly, the days only run correctly for days one through three. Cells after this just show false. I've poured over this all day and I cannot see where my formula is slipping up. If anyone can fix it, shorten it, or replace it with a VBA I would be eternally grateful.
I'm trying to make what I'm sure ought to be a simple calendar to track two types of work arriving and the time that they must be dealt with in.
Work type 1 (addressed) has a receipt day and then three days to be dealt with (labeled day 1, day 2, etc). Work type two (unaddressed) has a receipt day and severn days to be dealt with (again labeled day 1, day 2, etc). Only working days count so where a Saturday or Sunday are in between two work days I just have them labeled "Saturday" and "Sunday". The user will enter a date in a collum on the left (for receipt date in collum C) and then a work type in colum B. There is only ever one period of work per row and it is always either 3 or 7 working days after the recipt day. Cells without work days should be blank.
I have the Saturday and Sunday set up quite happily but am struggling with the Monday to Friday formula which is a monster:
=IF($C3=E1,CONCATENATE("Receive ",$B3),IF(OR(D3="Receive Addressed",D3="Receive Unaddressed"),"Day 1",IF(D3="Day 1","Day 2",IF(D3="Day 2","Day 3",IF(D3="Sunday",IF(OR(B3="Receive Addressed",B3="Receive Unaddressed"),"Day 1",IF(B3="Day 1","Day 2",IF(B3="Day 2","Day 3",IF($B3="Unaddressed",IF(D3="Day 3","Day 4",IF(D3="Day 4","Day 5",IF(D3="Day 5","Day 6",IF(D3="Day 6","Day 7",IF(D3="Sunday",IF(B3="Day 3","Day 4",IF(B3="Day 4","Day 5",IF(B3="Day 5","Day 6",IF(B3="Day 6","Day 7",""))))))))))))))))))
This goes in E4 (the first Monday) and then I would copy paste it over the rest of the cells. There seem to be two problems. The cells that are not work days are showing "False" rather than being blank, and, more problematicly, the days only run correctly for days one through three. Cells after this just show false. I've poured over this all day and I cannot see where my formula is slipping up. If anyone can fix it, shorten it, or replace it with a VBA I would be eternally grateful.
