Skip to content

GS Challenge 10 – Dates and Times

Welcome to your tenth Spreadsheet Life Google Sheets Challenge.

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

Google Sheets Challenge 10 – Practice File

Question 1

For question number one, your go 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 Google Sheets.

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

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

Times in Google Sheets 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.


Since the times are numbers, you can subtract them to get the difference. You then multiply by 24 to get the number in 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 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 hour’s 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 select both E5 and F5 and use the fill handle to copy the formulas down for the other rows.

Question 4

For question 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 Saturdays.

Question 4 – Solution

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

Select cell C8, and enter the formula:


The NETWORKDAYS.INTL function calculates the number of workdays between the start and end date. The 17 specified for the third argument causes the function to consider all Saturdays 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 Google Sheets Challenge number ten!

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 Google Sheets Challenges, you can find them 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