Date Example 1
Question
Using the blue reference cell, create the following in the appropriate yellow cells:
- Start month date (1st day of each month)
- End of Month (last day of the month)
- Number of days in the month
- Number of Net Working days in the month
- The % of networking days to days in the month
- The networking days assuming that Friday and Saturday are the weekend (only possible in Excel 2010 and above).
Date Example 2
Question
You need to build a spreadsheet that calculates the monthly rentals on a number of leases.
You are provided with the rental, the escalation dates, and escalation percentage.
Build formulas in the yellow cells so that your total at the bottom can match the suggested total.
Steps:
1- Extract the month of escalation into the cells E5 to E9 (e.g. an August escalation will say 8)
2- In row 17, extract the month from the date in the row below (e.g. September 2009 will show a 9)
3- In the rows 19 to 23, create a formula that will compare the current month to the escalation month for the relevant lease. If it is the escalation month then the rent must increase by the escalation %. If not it must just use the month before.
4- If you have created the formulas in rows 19 to 23 row by row, try and change the formula in cell F19 so that you can copy it down and across and all the calculations are still correct. This involves making sure the absolute and relative referencing is correct.
You are now told that the leases are for student accommodation and as a result there is no rent collected in December and January while the students are on leave.
5- Open up the hidden rows.
6- You have the input cells for the months of vacancy in C31 and C32.
7- Create a formula in row 34 (yellow cells) that will determine if the current month is one of the vacant months. If it is vacant then it must show 0%. If not then it must show 100%
8- Complete the formula in rows 38 to 42 to multiply the rental with the occupancy.
Date Example 3
Question
You want to know the last weekday (Monday to Friday) in any month based on the dates in cells B7 to B9.
Steps
1. In column D, determine the last day of the month
2. In column E, find out what day of the week the last date is
3. In column F calculate the maximum weekday allowed for our purposes (so if the day is a Saturday or a Sunday, it must show as a Friday here)
4. In column G, calculate how many days (if any) you must subtract from the last day of the month to get the last weekday.
5. In column H generate the date of the last weekday
6. In column I create a check to make sure that the date in Column H is a weekday.
To see the full course contents click here.