Skip to content

Excel Challenge 7 – Dates and Times

Welcome to your seventh Spreadsheet Life Excel Challenge.

In this article, I will give you four questions to test your ability to work with dates and times in Microsoft Excel. If you feel like you are up to the challenge, you can download the practice file below to try your hand at each question.

Excel Challenge 7 – Practice File

Question 1

For question number one, your goal is to calculate the total number of hours between the start time and stop time for each row.

Question 1 – Solution

To solve question one, you first need to understand how times work in Excel.

Times in Excel are actually just numbers. The only reason they appear as times is because of number formatting.

Behind the timely appearance, times are actually decimal values between zero and one. For example, the time of 6:00 AM is equivalent to 0.25.

Times in Excel are a fraction of 24. That is because there are 24 hours in a single day. The beginning of the day, or 12:00 AM, is represented as zero. And the end of the day is represented as one.

So 6:00 AM is equivalent to 0.25 because six divided by twenty-four is equal to 0.25.

Once you understand this concept, solving question one is fairly straightforward.

All you have to do is select cell D5 and enter the following formula.

=(C5-B5)*24

Since the times are numbers, you can simply subtract them to get the difference. You then multiply by 24 to get the number in the form of hours rather than a decimal between zero and one.

And to complete question one, you can use the fill handle to copy the formula down for each set of start and stop times.

Question 2

For question number two, your task is to calculate the date using the year, month, and day numbers.

Question 2 – Solution

To solve question two, start by selecting cell E5. Then enter the following formula.

=DATE(B5, C5, D5)

The DATE function takes the year, month, and day values and does all the work for you to return the calculated date.

You can then copy the formula down for the rest of the data using the fill handle.

Question 3

For question number three, you must write two different formulas to calculate the end time by adding the hours and minutes to the start time.

Question 3 – Solution

Begin by selecting cell E5 and then enter the following formula.

=(C5 + D5 / 60) / 24 + B5

This formula begins by dividing the minutes by 60 to transform them into hours. That result is added to the hours number.

This result is divided by 24 to transform into a time value.

And finally, that value is added to the start time to give you back the end time.

Now for the second formula, select cell F5 and enter:

=TIME(C5, D5, 0) + B5

In this formula, the TIME function takes the hours and minutes and turns those metrics into a time value. That time value is then added to the start time to give you the end time.

And those are the two formulas you can use to calculate the end time.

You can then use the fill handle to copy these formulas down for each record.

Question 4

For question number four, your goal is to calculate the number of days between the start and end date. You must also exclude the listed holidays and all Sundays.

Question 4 – Solution

To solve question number four, you will have to use the NETWORKDAYS.INTL function.

Select cell C8, and enter the formula:

=NETWORKDAYS.INTL(C4, C5, 11, E5:E8)

The NETWORKDAYS.INTL function calculates the number of workdays between the start and end date. The eleven specified for the third argument causes the function to consider all Sundays as non-workdays. And finally, the function does not count any days referenced in the fourth argument.

As a result, you get back the proper number of days between the start and end date.

And that officially completes the final question of Excel Challenge number seven!

Let Me Know How You Did

In the comments below, I invite you to leave a comment to let me know how you did. Did you find most of the questions to be rather difficult? Or were they easy?

Also, if you missed the first few Excel Challenges, you can find them listed below.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top