| | Feedback | Date functions | SumProduct | Access office leave database | | ||
This example uses the SUMPRODUCT function and Conditional Formatting to create a calendar which displays the dates which various staff have booked for their holidays. The report will automatically update itself, based on a single start date entered by a user. This flexible tool can be printed directly from Excel or exported to a web page.
Details of each persons booked leave can be entered in a simple list. The dates will be identified by the formulas and then displayed on the formatted report. It would also be possible to adapt this model to show other items such as attendance on training courses and give each item a different colour format and label on the report.
The list of holiday dates can be created and each range of cells given its own Defined Name.
| A28:A1000 | SNames |
| B28:B1000 | SFrom |
| C28:C1000 | STo |
Next, create a table with names on each row (G5:G7), and the dates for a month in each column (H4:AL4). In the last 4 columns (AI11:AL11) an indicator is added to show whether or not the dates in row 4 are still part of the same month as in H4. This will prevent data from being displayed when there are less than 31 days in the month.
 |
 |
| [H4] =DATE(YEAR($H$1), MONTH($H$1), 1) | The 1st date in the month of the date entered in H1 |
| [I4] =H4+1 | The date in H4 plus one day |
|
[H5] =SUMPRODUCT(($G5=SNames) * (SFrom<=H$4) * (STo>=H$4)) + IF(WEEKDAY(H$4, 2)>5, 2, 0) |
Sum the values in the 3 defined ranges where the name matches cell G5, the date in H4 is within the From and To range. Also if the weekday in H4 is a Saturday or Sunday, add 2. |
|
[AL6] =IF(AL9="Y", SUMPRODUCT(($G6=SNames) * (SFrom<=AL$4) * (STo>=AL$4)) + IF(WEEKDAY(AL$4, 2)>5, 2, 0), 0) |
Version of previous formula but only used in last 4 columns (AI:AL) and returns value of zero if 'N' (row 9) indicates a new month. |
| [AL9] =IF(MONTH(AL4)=MONTH(K4), "Y", "N") | Returns 'N' if the month of the date in the final 4 columns is different. |
| [H11] =DATE(YEAR(H1), MONTH(H1)+1, 1) | The 1st date in the month after the date entered in H1 |
| [H21] =H4 | Repeating date shown in H4 |
| [AL21] =IF(AL9="Y", AL4, "") | Only displays date if there is a 'Y' in row 9, otherwise cell is empty. |
| [H22] =IF(H5=1, "A", "") | Display 'A' if H5 has a value of 1 |
The formulas above create the text in the report, but not the red colouring for Absent days or the grey shading for weekends.
The coloured shading is created by applying Conditional Formatting rules to the range H22:AL24 (and any subsequent tables for future months).
If you wish to publish this as an HTML web page, it is necessary to put the final report on a different worksheet than your list of dates and the calculations table. The formulae will therefore need to be adapted.
The MakeHTML macro displayed elsewhere on this website will not capture the red or grey shading because it does not identify the colours created by Conditional Formatting. The Excel Save as Web Page command (file menu) does the job much better.
Click here for a web page example of the model shown above. It has been slightly modified with new data and by moving the report to a separate worksheet.
This spreadsheet requires a very large number of formulas in order to report on a small number of staff for just a few months. This can cause poor performance. This example contains some interesting examples of formulas and formatting but Excel may not be an ideal program for this kind of application. An Access database is available which can be larger and more flexible without performance issues.
|
The document below includes an Excel file containing this program.
|
|
| file: xl-holiday-planner.htm | © meadinkent.co.uk 2016 |  |
Last updated Apr16 | CMIDX S4 P4 Y |
