18 Ways To Work With Time & Date in Google Sheets

Last Updated on February 24, 2024 by Jake Sheridan

Working with time and dates in Google Sheets is sometimes harder than it needs to be. In this tutorial you will learn how to make it easier.

Working with Dates in Google Sheets

Date values are an important data type when you need to handle time-sensitive data or need to work with data captured over different time periods.

However, users may not be aware that there is more than one way to add dates into your Google Sheet.

First up is probably the first question you have about dates in Google Sheets:

1. How do I insert a date in Google Sheets?

Here are the best methods you can use to insert dates into Google Sheets:

Using valid time formats

The simplest way to insert a date in Google Sheets is by typing it yourself in a blank cell.

First, open the Google Sheets document where you want to insert a date.

Click on the cell in which you want to insert the date. This cell will become active, indicating it’s ready for you to input data.

For this example, let’s try to add a date in cell A1 of our sheet.

We recommend typing the date in one of the recognized date formats (e.g., MM/DD/YYYY, DD/MM/YYYY, or YYYY-MM-DD) directly into the selected cell.

Press Enter after typing your date to let Google Sheets process your input.

You can quickly check if the date was recognized by Google Sheets by double-clicking on the cell.

A date picker should pop-up if the cell recognizes your input as a valid date.

However, this method can be prone to errors.

For example, when typing the date “02/01/2024”, Google Sheets is not sure whether you mean February 1st or January 2nd.

What date format Google Sheets will follow in these circumstances may depend on the document’s locale settings.

To prevent these issues, we recommend entering specific dates using the DATE function instead.

Using the DATE Function

The DATE function allows users to create a date given a year, month, and day component.

The DATE function uses the following syntax:

=DATE(year, month, day)

The year argument must be given as a four-digit number. The month must be a number from 1(January) to 12(December). The day argument should refer to the day of the month of the desired date.

We can start by typing the string ‘=DATE(‘ into the selected cell. Afterwards, we’ll type our desired year, month, and day arguments.

In the example above, evaluating the formula =DATE(2024,1,2) gives us the date January 2nd, 2023.

Do note that DATE will automatically correct invalid dates by rolling over to the next month. For example =DATE(2024,01,32) will return February 1st, 2024.

Using the TODAY and NOW for Dynamic Dates

Google Sheets includes two functions you can use to generate dynamic dates in your spreadsheet.

You can enter the formula “=TODAY()” to display the current date whenever the sheet is recalculated.

The formula “=NOW()” displays the current date and time when the sheet is recalculated.

Using Keyboard Shortcuts

When using Google Sheets, it can help to know these quick keyboard shortcuts you can use to insert static timestamps and dates into your sheet.

You can insert the current date in a spreadsheet with the Ctrl + ; keyboard shortcut.

Select a cell, press the “Ctrl” key and then enter “;” (semi-colon). Excel takes a snapshot of the current date and enters it into the cell. This is a static value, meaning the date won’t change after you set it.

If you want to enter the current time, use “Ctrl + Shift + ;” instead. To put both the date and time in a cell, use the shortcut “Ctrl + Alt + Shift + ;”.

Using Data Validation Rules

You can make adding dates to your document easier by adding data validation to your spreadsheet.

With data validation, you can set up a rule that all values in a specific range must be a date.

When this rule is in place, the user can simply click on any cell where that rule is active and enter a date using a convenient date picker.

To add this type of rule to your spreadsheet, first select your target range and click Data > Data validation.

In the Data validation rules side panel, click on Add rule.

Under Criteria, select the option “Is valid date” and click Done.

The selected range should now have a data validation rule that checks if the cell’s value is a valid date.

Clicking on the cell will give the user the option to quickly select a date with a date picker element.

Formatting Your Dates

After inserting your dates in Google Sheets, you may want to change the date formatting. Google Sheets includes a variety of date format options for you to use.

To change the date formatting, select the cell or cell range you want to format and click Format > Number > Custom Date and Time

You’ll be able to choose from a list of popular date format options or create a custom format yourself using the text box.

By default, the time and date options that appear will be based on your spreadsheet locale

If you would like to build your format yourself, click the down arrow in the right corner of the menu text box and select an additional value.

2. How do I auto-fill dates in Google Sheets?

When setting up a table or tracker, you may want to generate a range of dates.

For example, you may want to populate a column with dates from the last 30 days starting with the current date.

Google Sheets makes this easy to do using the AutoFill feature.

Here’s how you can auto-fill dates in Google Sheets.

AutoFill Consecutive Dates

The most straightforward use case for the AutoFill feature is to create a series of consecutive dates.

Begin by opening the spreadsheet where you want to autofill consecutive dates.

Click on the cell where you want the series of dates to begin and manually type in the starting date.

If the cell doesn’t automatically format as a date, you can manually format it by selecting the cell and clicking Format > Number > Date.

This ensures that Google Sheets recognizes your input as a date.

After entering the starting date, hover your mouse over the lower-right corner of the cell until you see a small blue circle (called the fill handle).

Click and drag the fill handle down the column or across the row, depending on where you want to fill the dates.

Release the mouse button when you’ve reached the desired cell.

As you drag, Google Sheets automatically fills in the consecutive dates.

AutoFill Weeks and Months

We can use a similar approach to create a range of dates that are a week or a month apart.

Enter the first and second date to establish the pattern.

For weekly increments, enter a date that is exactly one week from the starting date in the next cell. For monthly increments, enter the date of the next month.

This step sets the pattern for Google Sheets to follow.

After entering the first two dates, select both cells.

Click and drag the fill handle down the column or across the row, depending on where you want to fill the dates.

Release the mouse button when you’ve reached the desired cell.

As you drag, Google Sheets automatically fills in the dates, maintaining the weekly or monthly increment.

You can use this technique to create consecutive dates with an arbitrary increment.

For example, you can set up the first two dates as seen below to create a range of dates that go backwards in time.

3. How to automatically fill rows with dates based on cell value?

In some scenarios, we want to automatically fill rows with dates based on another cell’s value.

For example, the user can input the starting date and the number of dates to generate after and the sheet should return the desired number of dates.

In this example, we’ll set up a sheet that outputs a specified number of dates that are one month apart starting from a specific date.

This particular example can be useful when setting up a tracker for recurring tasks or payments.

First, you’ll need to set up a table where the user can input the start date to use and the number of months to generate.

For this example, we’ll try to generate up to 12 dates spaced one month apart starting from January 22, 2024.

Next, select an empty cell and write a formula that outputs the start date.

For example, if our start date is in cell B2, we can type “=B2” to output the start date in this new cell.

In the succeeding row, we’ll use the following formula to generate the next date in the sequence:

=IF(ROW(A1)>=$B$1,””,EDATE($A$4,ROW(A1)))

This formula allows us to return the next date only if we have not yet reached the total number of dates required by the user.

With the EDATE formula, we can add a specific number of months to our starting date.

Since ROW(A1) returns a value of 1, our formula will return the exact date a month after our starting date of January 22, 2024.

When the user drags the formula down, the value returned by the ROW function will increase by 1 for each succeeding date.

In our example, when the row count exceeds 12, the formula will simply return a blank string.

When we change the number of months or the start date, our formula will automatically adjust its output.

We can modify the formula to instead increment by days instead of an entire month:

=IF(ROW(A1)>=$E$1,””,$A$4 + ROW(A1))

In the formula above, we’ll instead add the output of ROW to our starting date instead of using the EDATE function.

In our example above, we were able to generate 12 consecutive days starting from January 1st, 2024.

4. How do I change the date when a cell changes in Google Sheets?

We can use static timestamps to keep a record of when a particular cell in our spreadsheet was modified.

In the sheet below, we have a column where the user can enter email addresses.

We want the timestamp field to automatically generate a timestamp value when the user inputs text into the email_address field.

We can use the following LAMBDA formula to achieve this:

=LAMBDA(timestamp,IF(A2<>””,timestamp,””))(NOW())

This LAMBDA formula allows us to evaluate the NOW function a single time and store it as a static timestamp.

Let’s look into how the formula works.

  • LAMBDA accepts a single named argument that we’ll refer to as timestamp. The NOW function written after the LAMBDA function will be stored as a timestamp and will be substituted later when evaluating the IF formula defined in LAMBDA’s second argument.
  • The IF function checks if the target cell is not empty. If the cell is empty, we’ll return an empty string (“”). Otherwise, the IF function returns the value stored in the timestamp argument.
  • When a user enters a value in the target cell, the LAMBDA function will run the NOW function and copy its value into the IF function. Since the cell is no longer empty, the function will return the stored timestamp value.

We can also replace the NOW function with TODAY to set the output as the current date instead of a full timestamp.

5. How to autofill date and time when cell is updated in Google Sheets?

When working with a spreadsheet that is regularly updated, you may want to keep a record of the last time a spreadsheet was edited.

While Google Sheets does not have a built-in feature that returns a timestamp when a cell is updates in the spreadsheet, we can set up a custom script using Google Apps Script.

First, open your Google Sheets document and open the spreadsheet where you want to insert an automatic timestamp.

In this example, we want to update our timestamp in cell B1 every time we make an edit in this particular sheet.

Next, we’ll need to access the Google Apps Script editor so we can start writing our custom script.

Click on “Extensions” in the menu bar and select Apps Script from the dropdown menu.

You should now have a new tab open with the Google Apps Script editor.

 

In the script editor, enter the following Google Apps Script code:

function onEdit(e) {
var sheetName = "AutoFill Date and Time";
var targetRange = "B1";
var sheet = e.source.getSheetByName(sheetName);
var range = e.range;
if (sheet && range.getA1Notation() !== "" && sheet.getName() === sheetName) {
sheet.getRange(targetRange).setValue(new Date());
}
}

 

Because we’ve set the function name to onEdit(), this script triggers every time a cell is edited.

The onEdit() function checks if the cell that was just edited is in the sheet named “AutoFill Date and Time”.

If this is the case, the function will update the value in cell B1 of the same sheet with the current date.

After entering the formula, click the Save icon and name your project in the Apps Script editor.

Head back to your spreadsheet and try modifying values in your table.

If everything is set up correctly, cell B1 should now be updated with the current timestamp.

6. How do I automatically insert a timestamp in Google Sheets?

The simplest way to insert a static timestamp in your spreadsheet is by using a keyboard shortcut.

Google Sheets comes with a few commands that let users quickly input the current date or time into a cell.

  • Insert the current date using the keyboard shortcut Ctrl + ; (Cmd + ; for Mac)
  • Insert the current time using the keyboard shortcut Ctrl + Shift + ; (Cmd + Shift + ; for Mac)
  • Insert the current date and time using the keyboard shortcut Ctrl + Alt + Shift + ; (Cmd + Shift + ; for Mac)

However, you may want to insert a dynamic timestamp that updates automatically.

Luckily, Google Sheets comes with a few built-in function you can use to insert dynamic timestamps.

We can use the TODAY function to insert the current date.

Simply select an empty cell and type the formula =TODAY().

The date is updated dynamically, meaning it changes to reflect the current date whenever the spreadsheet recalculates.

This recalculation can be triggered by opening the spreadsheet, making edits, or according to any set recalculation settings in the file.

If you would like to insert the current date and time, we can use the NOW function to insert the current date and time.

To change the recalculation settings, click File > Settings.

In the Calculation tab, you can change when Google Sheets recalculates the NOW and TODAY functions.

By default, Google Sheets updates the NOW and TODAY functions every time the user makes a change in the document.

However, we can also set it to automatically recalculate every minute or every hour.

7. What is the format for date () in Google Sheets?

The output of the DATE function will be formatted depending on the user’s location.

For example, users with a US-based Google Account will have a default date format of MM/DD/YYYY.

To change the date format of the DATE function, you can use the following methods:

Using the Format Options

We can adjust the date formatting of any value recognized as a date in Google Sheets.

First, select the cell or cell range containing the date values you want to format.

Click Format > Number > Custom date and time.

You should now see a Custom date and time formats dialog box where you can browse and select from a list of common date formats.

Users also have the option of creating their own date formats using the provided textbox.

After selecting your desired date format, click on Apply.

In the example above, we used the Format options to apply the date format “yyyy-mm-dd” to the output of our TODAY function.

Using the TEXT Function

Another way to apply formatting to dates is through the TEXT function.

The basic syntax of the TEXT function is =TEXT(value, format).

Here, value refers to the numerical value you want to format, and format is a text string that defines the desired format.

In the example above, we used the formula =TEXT(TODAY(),”yyyy-mm-dd”) to apply a specific date formatting for the output of the TODAY formula.

Changing the Locale of your account

You may want to change the locale of your Google Sheets document to adjust the default date formatting when inputting new date information.

Start by opening the spreadsheet you want to change the locale of.

Click File > Settings to access the general settings for this particular spreadsheet.

Next, head to the General tab and use the dropdown menu to change the locale of your spreadsheet.

Click on Save and reload to apply the new locale setting.

8. How to change date format in Google Sheets from MM DD YYYY to DD MM YYYY?

Different countries use different date formats, a notable example being the difference seen between the American and European formats.

In the United States, the date format follows the sequence of month, day, and then year (MM/DD/YYYY).

Most European countries, along with many other parts of the world, use a day, month, year sequence (DD/MM/YYYY).

Here’s how you can change the date format in Google Sheets to DD/MM/YYYY.

Navigate to Google Sheets and open the spreadsheet containing the dates you want to reformat.

In this example, we have a dataset of orders with a column containing the purchase_date.

The column is formatted using the American date format (MM/DD/YYYY) but we want to convert it to a European date format (DD/MM/YYYY )

Select the cell or range of cells with dates you want to reformat.

Click on the cell or drag your mouse across a range of cells that contain the dates in MM DD YYYY format.

Click Format > Number > Date to access the Custom date and time formats dialog.

In the custom format field, look for the format that follows the European date format.

In Google Sheets, each date presented uses the date August 5, 1930. Thus, we’ll need to select the option “05/08/1930”.

After selecting the format, click on “Apply.”

Your selected date range should now follow the European format where the date follows the sequence of day, month, then year.

9. How do I remove the year from a date in Google Sheets?

Have you ever needed to remove the year from a date?

Here’s how you can format your date value to only show the month and day.

Changing the Date Formatting

First, we’ll need to select the cell or range of cells containing the dates we want to reformat.

Use your mouse to select across a range of cells that contain the dates.

In our current example, we’ll be revising the date formatting of the dates in column A of our table seen above.

Next, click on “Format” in the menu bar at the top of your screen.

This will open a dropdown menu with various formatting options.

In the dropdown menu, hover over “Number” and select the “Custom date and time” option.

This will open a custom date and time format window where you can specify exactly how you want your date to be displayed.

In the Custom date and time format window, we can delete the year component of the format by clicking on the component and selecting Delete.

Alternatively, you can browse through the list of available date formats for an option that does not show the year.

After customizing the date format, click on “Apply.”

The selected range should now no longer display the year.

Creating a New String Without the Year

Let’s say you have a series of dates you want to sort.

However, you want your dates to be sorted by month and day regardless of the year.

To make this possible, you may have to convert your dates into text values that follow a format that does not indicate any date.

Here’s how you can remove the year from the date.

Select the range containing the dates you would like to remove the year from.

In this example, we have a table containing birthday celebrants. We’re only concerned with ordering the celebrants by month and day, ignoring the year entirely.

For example, John’s actual birthdate is a few years after Jack’s but when considering the birthday (month and day) John’s birthday comes before Jack’s.

One way to remove the year in our date value is by formatting our numbers into text strings of a specific format that does not display the year.

Thankfully, Google Sheets comes with a TEXT function that allows you to do just that!

For example, we can use the formula =TEXT(A18, “mm-dd”) to convert John’s birthdate to the string “08-15” in a new column.

We’ll use the fill handle to drag the formula down and find the birthday string of the remaining people on the list.

Since our dates are formatted with the month follows by the date, we can easily use the Sort tool in Google Sheets to sort our range starting from the first birthday celebrant of the year.

10. Why does Google Sheets change my date format?

Google Sheets might change your date format automatically due to its default settings and the regional settings of your spreadsheet.

Google Sheets bases its default date format on the locale and regional settings of your account or the specific spreadsheet.

For example, a sheet set to the US locale will typically format dates as MM/DD/YYYY, whereas one set to a UK locale will format dates as DD/MM/YYYY.

If your dates are being reformatted, it might be due to a discrepancy between the format you’re entering and the default format for your sheet’s locale.

To prevent auto-formatting your dates, you may want to add Plain Text formatting to the column or range where you plan on entering your dates.

By setting your cells to Plain Text formatting, Google Sheets will treat the incoming input as plain text and not as date values.

11. How to convert date in text format to date format in Google Sheets?

First, open Google Sheets and open the spreadsheet that contains the text dates you want to convert.

If you want to keep the original text dates, insert a new column next to the dates’ column where the converted date objects will be placed.

This step ensures that your original data remains intact while you work on the conversion.

We can use the DATEVALUE function to convert the text date to a date object.

This function interprets the text as a date and converts it to Google Sheets’ standard date format.

Click on the cell where you want the converted date to appear, and type in the formula =DATEVALUE(“text_date”) where “text_date” is the cell reference containing the text date.

If you have multiple text dates to convert, you can drag the fill handle down or across to fill other cells with the DATEVALUE formula.

This action applies the date conversion to all selected cells.

Since no formatting has been applied to the date values, the dates appear as numerical data.

To convert these dates into a readable format, we’ll need to apply our preferred date format.

Select the cells with dates and click Format > Number. Choose the date format you prefer.

You may need to click Custom date and time to set up your preferred date format.

Working with Time in Google Sheets

Here are the different ways to handle time in your spreadsheets:

12. How do I insert time in Google Sheets?

Inserting time values into a cell in Google Sheets is quite straightforward.

Here are the best methods you can use to insert time into your Google Sheets.

Using valid time formats

First, click on the cell where you want to insert the time. In our current example, we want to insert time in cell A1.

Type the time directly into the cell. Google Sheets accepts various time formats that the user could input.

You can use different formats like “HH:MM” or “HH:MM AM/PM”. For instance, you could type “5:30 PM” or “14:30” into cell A1. After typing, press Enter.

After entering the time, Google Sheets should automatically recognize it as a time value and format the cell accordingly.

If Google Sheets does not automatically format the time correctly, you can manually set the format.

Click on the cell with the time, then go to Format > Number > Time. This step ensures that the cell is correctly identified as a time format.

Using TIME function

You also have the option to use the TIME function in Google Sheets to create a time value based on individual hour, minute, and second components.

Start by clicking on the cell where you want the time value to appear.

Next, enter the TIME function into the cell.

The syntax of the function is as follows:

=TIME(hour, minute, second)

For instance, if you want to represent 3:45:30 PM, you would enter =TIME(15, 45, 30) into cell A1. Press Enter after typing the formula.

Once you’ve evaluated the formula, Google Sheets will automatically calculate the time and display it in cell A1.

If the time isn’t in the format you prefer, you can change it.

Click on the cell with the time, go to Format > Number > Custom date and time, and choose the time format you like.

You can select from the predefined formats or create a custom format if you have specific needs.

Using a keyboard shortcut

In Google Sheets, you can insert the current time using a keyboard shortcut.

First, click on the cell where you want to insert the current time.

  • On Windows: Press Ctrl + Shift + ; (semicolon).
  • On Mac: Press ⌘ + Shift + ; (semicolon).

This shortcut will insert the current time into the selected cell. Note that this time is static and will not update automatically.

13. How do I format hours and minutes in Google Sheets?

In some scenarios, you may need to format time values in a specific format.

Here’s how you can format cells to display your date as just hours and minutes.

Open your Google Sheets document and select the range of cells containing time values you wish to format.

In our current example, we’ll select the range A8:A12.

With the cells selected, go to the menu bar and click on Format. In the dropdown menu, hover over Number to reveal the formatting options.

Click Custom date and time to choose a new time format to apply to your range.

You should now see the Custom date and time formats dialog box.

Here, you can create a custom time format. Click on the down arrow next to the format preview and select the time components you want to include.

For hours and minutes, you might choose “Hour” and “Minute”.

You can also choose the format for these components, like whether hours are displayed in AM/PM format.

Ensure that you add additional characters such as a colon “:” between the hour and minute components to achieve a readable format.

Click on Apply to proceed with applying the current time formatting to the range selected earlier.

The selected time values will now display the time in the format you’ve chosen.

Do note, however, that this formatting only changes how the data looks, not the data itself.

14. How do you calculate time duration?

To calculate time duration in Google Sheets, you can subtract the start time from the end time.

Here’s how you can do it step by step.

Enter the start time and end time in separate cells.

Make sure you format the cells as a time format. You can do this by selecting the cell, then clicking on Format > Number > Time.

In our current example, let’s say B1 contains a start time of 8:00 AM and B2 contains our end time of 5:00 PM.

In another empty cell, subtract the start time from the end time by entering the formula =B2 – B1.

This will give you the duration between the two times.

If the duration isn’t formatted as a duration, you can change the format of the cell with the duration by selecting the cell, clicking Format > Number, and then choose “Duration”.

If your duration might cross over midnight, you might get a negative result. To fix this, use the formula =MOD(B1 – A1, 1) instead.

Wrapping our subtraction operation with MOD handles the transition over midnight correctly.

In the example above, we are able to find out that a work shift from 9PM to 7AM the next day covers ten hours.

15. How do I calculate time difference in minutes in Google Sheets?

Calculating the time difference in minutes in Google Sheets involves a few steps to ensure you get the correct duration in minutes format. Here’s a detailed guide:

Input the start time and end time in separate cells.

Ensure the cell is formatted as ‘Time’ by selecting the cell, going to Format > Number > Time.

In a new cell, calculate the difference between the end time and the start time by entering the formula =MOD(B2 – B1,1).

This will give you the duration between the two times in a time format.

The MOD function will help handle cases where the duration between the start date and end date cross midnight.

To convert the time duration from our formula into minutes, multiply the result by 1440 (since there are 1440 minutes in a day).

In this example, we’ll use the formula =C2 * 1440. After pressing Enter, we should now see the time difference in minutes.

Ensure the cell with the minutes is formatted as a ‘Number’. You can do this by selecting the cell, clicking on Format > Number > Number.

16. How do I autofill time in Google Sheets?

The AutoFill feature in Google Sheets can be a great way to quickly populate a column or row with sequential time values.

Here’s how you can do it step by step:

Select the first two time values in your desired series.

The duration between these two values will determine how the series will be calculated with the AutoFill feature.

In our example above, we want to create an hourly tracker where each time value is one hour after the previous value.

In this case, we’ll start by typing 7:00 AM and 8:00 AM into our table/ Do make sure the cell is formatted as a time by going to Format > Number > Time if it’s not automatically recognized.

Select both cells and drag the fill handle (small blue circle) at the bottom right corner of the range of selected cells.

Release the mouse button when you’ve highlighted the desired number of cells.

Google Sheets will automatically fill in the subsequent cells with increasing time values, continuing the pattern you started.

This method also works for various durations, such as every 30 minutes.

The user simply needs to adjust the time difference between the first two entries of the series.

17. How do I insert a date and timestamp in Google Sheets?

Timestamps are crucial for accurately tracking and recording the exact date and time of an event or action in a dataset.

A timestamp typically combines both the date and the time into one detailed value, usually in the format YYYY-MM-DD HH:MM:SS.

Understanding how to insert and manipulate dates and timestamps is a helpful skill when working with data in Google Sheets.

Let’s take a look at the quickest and most reliable ways to insert a date and timestamp in your document.

Using keyboard shortcuts

To insert a timestamp in Google Sheets, we can use the built-in keyboard shortcut Ctrl+Alt+Shift+;.

Do note that the resulting value is a static timestamp and will not be updated.

Using the NOW Function

Another way we can insert a timestamp in Google Sheets is by using the NOW function.

The NOW function allows us to return the current date and time as a value in our spreadsheet.

Simply type the formula =NOW() into an empty cell to create a timestamp in your document.

The value will automatically update after every edit made to the spreadsheet.

Adjusting the Formatting

We can change the formatting of our date values to convert them into timestamps.

To do this, simply select the range of cells you wish to convert into timestamps. Next, click on Format > Number > Date time.

The selected range of values should now be formatted as a timestamp.

18. How do I add hours and minutes in Google Sheets?

Adding hours and minutes in Google Sheets can be done by using simple arithmetic operations and ensuring the time format is correctly applied.

Here’s a step-by-step guide for adding specific hours and minutes to a given time:

We can begin by entering the starting time in a blank cell.

Input the time in a recognized format, like “1:50 PM” or “5:00 AM”. After entering the time, Google Sheets should automatically recognize it as a time value.

In our current example, let’s say cell A2 contains “12:00 PM”.

Next, decide how many hours and/or minutes you want to add.

For instance, you might want to add 30 minutes or 2 hours and 30 minutes. Note these values down or type them into another part of your sheet for reference.

For this tutorial, let’s try adding an hour and 30 minutes to our starting time.

In a new cell, we’ll use a formula to convert the hours and minutes you want to add into a fraction of a day because Google Sheets interprets time as a fraction of a day (1 hour = 1/24 day, 1 minute = 1/1440 day).

To find the fraction to add to our starting time, we can use the following formula:

=B1+(B3*60+B2)/1440

The formula above finds the total number of minutes to add by multiplying the hour component by 60 and adding it to the minutes component.

The resulting sum is then divided by 1440. The result of this is then added to our starting time in cell B1.

In our example above, we determined that adding an hour and 30 minutes to our starting time of 12:00 PM results in a new time of 1:30 PM.