Sometimes, it is necessary for Excel projects to be handled entirely by formulas as opposed to using VBA and creating a fully bespoke VBA system. This project was requested with the specifications that it must be formula based which meant it could be used, updated and amended on any mobile device such as an iPhone or Android.
The brief was to allow the user to list all their projects and sub projects on a worksheet and then assign a start date and an end date for each project, instantly highlighting the dates within the parameter along the specific row. Weekends and Bank Holidays should be excluded.
This was achieved using an IF/AND formula and complimented with conditional formatting to create the task bar effect you can see in the screenshot example here.
Whenever a date is changed, the task bar automatically changes to reflect the new date parameters.
A formula was created to determine if the cell represented a day within the start and end dates and if so would need to be highlighted. The most efficient way was to instruct this formula to check if the day/cell was in the date parameters and if so, just put a dot in the cell.
The date for the column is added to cell F1 and the $ within the formula instructs Excel to make the row (1) permanent wherever you drag the formula. The start date and end dates are on the row in cells D3 and E3. Again the $ makes sure that wherever you drag the formula to it keeps the columns fixed.
The most important part is now managed by Conditional Formatting. As we have put a dot in the cell to be the trigger we tell Conditional Formatting to colour the cell blue but also the dot we have added should also be the same colour just for a more professional look and feel. If the date is not in the range then the formula instructs the cell to be blank.
The formula can then be copied and pasted into any cell on the row where you want to check for the date within the parameters.
Please feel free to ask us a question, or alternatively leave a comment/question below and we'll get an answer asap for all to see and learn from.