Date Examples 3

Please purchase the course before starting the lesson.

DATE Ex 11

You need to determine the average days to deliver goods.

  • In columns C and D you have extracted the Invoice month but the month and year are shown in separate columns. In column E (yellow cells) you need to create a valid Excel date with the day always assumed to be the 1st day of the month.
  • In column H you have received the Delivery Date but in a format that Excel doesn’t recognize as a date. In column I, copy and paste column H and then use a tool in Excel to convert the dates into a valid Excel date.
  • In Column J, calculate the number of days between invoice date (column E) and delivery date (column I).
  • In cell J9, work out the average of the delivery days.
  • In Column L, determine what day of the week the delivery occurred on (based on column I). In column M set up a formula to say either weekday or weekend depending on the result in column L.
  •  cell M5 count how many deliveries happen on a weekend. In M6 count how many happen on a weekday.
  • In N5 & N6, determine the percentage of deliveries that happen on a weekday and weekend.
  • In N9, calculate what is the AVERAGE IF the delivery is on a weekend.

DATE Ex 12

You need to determine the average days to deliver goods.

  • In columns C and D you have extracted the Invoice month but the month and year are shown in separate columns. In column E (yellow cells) you need to create a valid Excel date with the day always assumed to be the 1st day of the month.
  • In column H you have received the Delivery Date but in a format that Excel doesn’t recognize as a date. In columns K to Q you must create formula to convert the delivery date into an Excel recognizable date. To do this do the following:
    • In column K you need to FIND where the first full stop occurs.
    • In Column L you need to FIND where the second full stop occurs.
    • In Column M, you need to calculate the gap between the full stops.
    • In Column N, O, and P extract the Day, Month and Year from column H..
    • In Column Q, convert the information in column N, O, P into a valid DATE.
    • In column R, calculate the number of days from invoice to delivery.
  • In cell R77, calculate the average number of days till delivery and compare to the previous example (should be the same)

DATE Ex 13

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.

To see the full course contents click here.

Back to: Advanced Excel Course > Date functions and issues