Date and time in Google Sheets: enter, format and convert dates and time in your sheet

Today we'll discuss what you can do with dates and time in a Google spreadsheet. Let's take a closer look at how you can enter date and time into your table, and how to format and convert them to numbers.

How to insert date and time in Google Sheets

Let's start by entering the date and time into a Google Sheets cell.

Tip. Date and time formats depend on the default locale of your spreadsheet. To change it, go to File > Settings. You'll see a pop-up window where you can set your region under the General tab > Locale. Thus, you'll ensure those date and time formats you're accustomed to.

There are three ways to insert date and time into your Google spreadsheet:

Add date and time manually

Note. No matter how you want the time look like at the end, you should always enter it with a colon. This is essential for Google Sheets to distinguish between time and numbers.

It may seem to be the easiest way but locale settings that I mentioned above play a vital role here. Every country has its own pattern for displaying date and time.

As you all know, the American date format differs from the European one. If you set "United States" as your locale and type in the date in the European format: dd/mm/yyyy, it simply won't work. The entered date will be treated as a textual value. So, pay attention to that.

Tip. You can add today's date much quicker. Type @ in the required cells and continue with the word today: See your prompt turn to today's date in Google Sheets.

Google Sheets will recognize your prompt and suggest you add Today's date. Click it, and your text will turn into today's date: Prompt appears as today's date in Google Sheets.

Confirm by pressing Enter.

Make Google Sheets auto-populate your column with date or time

Fill in a few cells with the required date/time/date time values.

Select these cells so you could see a small circle at the bottom right corner of the selection: Enter a few time units.

Click that circle and drag the selection down, covering all required cells.

You'll see how Google Sheets automatically populates those cells based on two samples you provided, retaining the intervals: Make Google Sheets enter time for you.

Use key combinations to insert current date and time

Place the cursor into the cell of interest and press one of the following shortcuts:

  • Ctrl+; (semicolon) to enter the current date.
  • Ctrl+Shift+; (semicolon) to enter the current time.
  • Ctrl+Alt+Shift+; (semicolon) to add both, current date and time.

Later you'll be able to edit the values. This method helps you bypass the problem of entering an incorrect date format.

Take advantage of Google Sheets date and time functions

TODAY() — returns the current date to a cell.

NOW() — returns the current date and time to a cell.

Note. These formulas will be recalculated, and the result will be renewed with every change made in the table.

Here you are: you've placed date and time to our cells. The next step is to format the information to display it the way you need it.

As it is with numbers, you can make your spreadsheet return date and time in various formats.

Place the cursor into the needed cell and go to Format > Number. You can choose between four different default formats or create a custom one using Custom date and time setting: Choose the existing date and time format or create a custom one in Google Sheets.

As a result, one and the same date looks different with various formats applied: One date – different number formats.

As you can see, depending on your needs, there are a few ways to set the date format. It allows displaying any date and time value, from a day to a millisecond.

Make your date/time a part of Data validation.

In case you need to use date or time in Data validation, proceed to Data > Data validation in the Google Sheets menu first:

  • As for dates, just set them as criteria and choose the option that suits you best: Date in Data validation.
  • As for time units, since they are absent from these settings by default, you will need to either create an additional column with time units and refer to this column with your Data validation criteria (List from a range), or enter time units directly to the criteria field (List of items) separating them by a comma: Time in Data validation.

Insert time to Google Sheets in a custom number format

Suppose you need to add time in minutes and seconds: 12 minutes, 50 seconds. Place the cursor to A2, type 12:50 and press Enter on your keyboard.

Note. No matter how you want the time to look like at the end, you should always enter it with a colon. This is essential for Google Sheets to distinguish between time and numbers.

What you'll see is Google Sheets treating your value as 12 hours 50 minutes. If you apply the Duration format to A2, it'll still show the time as 12:50:00.

So how can you make Google spreadsheet return only minutes and seconds?

  • Type 00:12:50 to your cell.

    To be honest, this one may turn out a tiresome process if you need to enter multiple timestamps with minutes and seconds only.

  • Type 12:50 to A2 cell and put the following formula into A3:

    =A2/60
    Then apply the Duration number format to A3. Otherwise, your table will always return 12 hours AM.

  • Use special formulas.

    Input minutes to A1, seconds — to B1. Enter the following formula to C1:

    =TIME(0,A2,B2)
    In order to delete excess symbols from our time, set the format again. Go to Custom date and time and create a format that will show only elapsed minutes and seconds: Custom time format.

    The TIME function refers to cells, takes the values and transforms them into hours (0), minutes (A1), and seconds (B1). Time formula.

Convert time to decimal in Google Sheets

Let's move on to various operations you can do with date and time in Google Sheets.

There may be cases when you need to display time as a decimal rather than "hh:mm:ss" to perform various calculations. Why? For example, to count per-hour salary, since you can't perform any arithmetic operations using both, numbers and time.

But the problem disappears if time is decimal.

Let's say column A contains the time you started working on some task and column B shows the end time. You want to know how much time it took, and for that, in column C you use the formula below:

=B2-A2

Copy the formula down cells C3:C5 and get the result in hours and minutes. Then transfer the values to column D using this formula:

=$C2

Then select entire column D and go to Format > Number > Number: The first steps of converting time to decimal.

Unfortunately, the result you'll get at first won't say much. But Google Sheets has a reason for that: it displays time as a part of a 24-hour period. In other words, 50 minutes is 0.034722 of 24 hours.

Of course, this result can be used in calculations.

But since we're used to seeing time in hours, you may want to introduce more calculations to your table. To be specific, multiply the number you've got by 24 (24 hours): Enter time as decimal.

Now you have a decimal value, where integer and fractional reflect the number of hours. To put it simply, 50 minutes is 0.8333 hours, while 1 hour 30 minutes is 1.5 hours.

Text-formatted dates to date format with Power Tools for Google Sheets

There's one quick solution for converting dates formatted as text to a date format. It's called Power Tools. Power Tools is an add-on for Google Sheets that allows you to convert your information in a couple of clicks:

  1. Get the add-on for your spreadsheets from Google Sheets webstore.
  2. Go to Extensions > Power Tools > Start to run the add-on and click the Convert tool icon on the add-on pane. Alternatively, you can pick the Tools > Convert tool right from the Power Tools menu.
  3. Select the range of cells that contain dates formatted as text.
  4. Check the box for the option Convert text to dates and click Run: Convert format on Google Sheets with Power Tools.

Your text-formatted dates will be formatted as dates in just a few seconds.

I hope you've learned something new today. If you have any questions left, feel free to ask them in the comments below.

Next time you'll learn how to calculate the time difference and sum dates and time together.

Table of contents

101 comments

  1. I want to store automatically date & time when data entered in sheets how can i do

  2. Can Google enter full dates (IE 2024-06-18) if I only enter the day and month, IE 18/6 for the 18th of June in the current year? Excel and OpenOffice both allow this. I'm wondering if Google Sheets does too. If so, how do I access this function, as at the moment, it's not doing so. BTW, I am in Australia, so 2024-06-18 instead of 2024-18-06.

  3. hi!
    can you help me with my google sheets
    I want to show the cut-off date
    for example, I want to show every 20th of the month
    what formula will I use?

    • Hi ANN,

      Sorry, could you please specify your task? Do you want the list of all months but only the 20th day? Or do you need to show 20th of each month when it's actually the 20th day? Or maybe for each previous month only?

  4. i want day by day automatically date changing formula in google sheet

  5. How to make one time can be select once only

  6. Hello,

    I am looking to create a descending list in column A for each month on each sheet/page.

    I am trying to use a date format of: mm/dd/yy for each of the 12 pages, one for each month.

    So, Column A1 would be the first day of that month, and A31 would be the last day of that month. One page per month. (I have the 12 pages/labels for the months figured out.)

    Thank you for your time.

  7. Are there any formulas in the Google sheets to know when and at what time did the google sheet got created and when and at what time it was last updated?

    Currently i can see it only in the version history option. But i need to update the created date and time and last edited date and time information directly in the spreadsheet without checking in version history.

    Thanks

  8. Hello,
    I opened a csv file with google sheets and there are a lot of dates like this one 9/29/2022, 10:04:04 PM (mm/dd/yyyy hh:mm:ss am/pm). This csv file i have to import it in a program wich read the dates dd/mm/yyyy hh:mm:ss am/pm and at dates like this 9/29/2022, 10:04:04 PM it show error "invalid date" . Is possible to change 9/29/2022 in 29/09/2022 without doing this manually (too many dates to change manually) ?

  9. HI... i want to record the time when a specific event (e.g., "Permission") occurs in cell G, and display the time in cells H & another event occurs (e.g., "Return") and display the time in cells J, & Assuming i want to calculate the time duration in cell k.

  10. I am wanting to autofill the dates of a month in google sheets but I only want to have it autofill the dates for Mondays and Wednesdays. How do I do this?

  11. Hello Natalia,

    I am trying to create a formula where "IF Date is "Today" Then, "Due Today" type of statement.
    Additionally, "If Date is "Past Due" Then,"Assignment Overdue" ...

    Not sure exactly how to put this together or if it is possible.

    Im actually looking to insert this into a "Monday.com" formula box but this should also be universal with the formula structures in Google.

    Thanks in advance.

  12. I am trying to validate a complete date in google sheets, and have been using the "isDate()" function. However, it validates true even if the date is just a month/year although all the documentation I've seen says that it requires a month/day/year. Is there a way to validate that a cell and require the month, day and year? It seems that it just defaults to the first day of the month.

    Ex: Cell A1 contains "10/1929"
    =isDate(A1) returns true
    =Day(A1) returns 1

    • Hello Mitch,

      Google Sheets picks up dates written in different formats. Hence, you're right, it treats 10/1929 as October 1st, 1929. You'll see the same in the formula bar if you select the cell. I'm afraid this is a matter of the format visible to you which cannot be handled with formulas.

      However, you can try some kind of a workaround. For example, use the IF function to check if the Day from A1 equals 1. And if so, mark the date as potentially incomplete.

  13. how to insert drop down date and time in one chell

  14. I have a sheet with contacts from different time zones within the US. How can I show the time zone of each contact? is there a way to convert time to different time zone so I can have a single time for every contact?

    • Hello Nasir Khan,

      I'm afraid there are no standard functions to work with the time zones. You will have to add formulas that add the required amount of hours to show the same time zone for each contact.
      Alternatively, you can use scripts. You may try to find the solution here – an overview of Google Apps Script with a lot of helpful content and links: https://developers.google.com/apps-script/overview

  15. I am using google sheet and applying formula =IF(L2="Fully Consumed",TODAY(),""), it means that if condition is fulfilled in L2 Column i.e. "Fully Consumed" then Current Date should be in N2 columns, it is applying properly but Date is changed on daily basis as current date which should not be done, I want that date should be applied once then should not be changed as date changed. Is there any proper solution of my problem?

  16. Hello There,
    I need to enter the same date for 5 rows (2023-06-12) and then the next day for the next 5 rows (2023-06-13), etc, etc. The sheet is configured to follow date order when it is dragged down. I have tried manually adding about 15 rows (3 dates) and then dragging down, but it still converts backs to putting one day in each row. Please help! Thanks :)

    • Hello Susan,

      Here's what you can do:

      1. Fil your column (start with B2) with as many days as you need (one instance of each day: 2023-06-12, 2023-06-13, 2023-06-14, etc.). You can do it quickly with the standard dragging.
      2. Select the cells with days, press & hold Ctrl on your keyboard and drag everything to 4 columns to the right. You'll have 5 columns of the same day on each row.
      3. Enumerate each row in column A and each column in row 1 (1,2,3,4, etc).
      4. Select all the data (including the numbered headers and labels on the left) and use our Unpivot Table tool. It'll place all the dates just as you need.

      Use the dragging on steps 1-3 to speed up the process. It'll be much quicker than entering dates by groups one by one.

      • Thanks for answering so quickly, Unfortunately, this did not work the way I need it to. I ended up with just 3 coloumns on a new spreadsheet ( so all the other data was missing, and the dates ended up like this...
        2023-05-08
        2023-05-09
        2023-05-10
        2023-05-11
        2023-05-09
        2023-05-10
        2023-05-11
        2023-05-12
        2023-05-10

        where as I need.....
        2023-05-08
        2023-05-08
        2023-05-08
        2023-05-08
        2023-05-09
        2023-05-09
        2023-05-09
        2023-05-09
        2023-05-10

        Thanks!

        • Hello Susan,

          Please make sure you enumerate both columns & rows, and include these numbered labels into the Unpivot.
          You can send me the video illustrating what you do to support@ablebits.com (referring to this comment). I'll watch and suggest what may be going wrong.

  17. We need to input today's date using a tablet or phone, entering business incomes/transaction.
    On the computer ctrl+; works great, but how to do this on a tablet, iPad specifically?

  18. I WANT THE RESULT IN THE SPECIFIED CELL AFTER SPECIFIC TIME.

    HOW TO GET IT.

    MEANS,

    =A1+A2

    IS FORMULA WRITTEN IN A3 BUT THE RESULT IN A3 MUST BE VISIBLE SAY AFTER 5 MINUTES (SPECIFIC TIME AND NOT IMMEDIATLY. HOW TO GET IT??

    • Hello CHAKOR,

      I'm afraid it's impossible with the standard Google Sheets functions. You need to use a script for the task. Since we do not cover the programming area (script-related questions), I can suggest looking for a solution here – an overview of Google Apps Script with a lot of helpful content and links:
      https://developers.google.com/apps-script/overview

      I hope you’ll find this information helpful. Sorry I can't assist you better.

  19. This worked =TODAY()
    Thank you.

  20. I am creating a spreadsheet for my online classmates.
    We are all over the world, and I am wondering if there is a way to have a cell show what time it is in each person's locale (updating itself every minute to the current time). Can I tell the cell to show current Eastern Standard time in one cell, and current Swedish time in another cell?

    • Hello kargelc,

      If you're in Eastern Standard Time, set this time zone in your spreadsheet settings first. Then =NOW() will show your current time.
      Swedish is located 5 hours away from EST. So use =NOW() + 5/24 to show Swedish time in another cell.

  21. Hello!
    I am looking to have a date pop into a separate column, when a checkbox is marked complete
    For example checkbox is in column C, i have completed my task and once i check the box, todays date would pop into Column E
    Thank you for your help!

    • Hello Wilfredo,

      You need to use the IF function along with TODAY. However, TODAY is a volatile function. It recalculates itself in all cells each time anything is edited somewhere on the sheet. There are a few workarounds to this:

      • You can manually turn new TODAY formulas into values. This way, you will prevent previous dates from changing.
      • Or you can manually enter the current date by pressing Ctrl + ; on your keyboard.
      • Or use scripts to solve the task. But since we don't help with scripts, you may try to find the solution here – an overview of Google Apps Script with a lot of helpful content and links: https://developers.google.com/apps-script/overview

  22. hi im new to google sheets, i have a lessons time table that has Period 1 through 5 along the top and the staff members down the side. i have entered the correct formula to have it colour coded so when a staff member puts in their data for eg; "Period 3" it automatically gives their cell the same colour assigned to that staff member etc.

    As each period passes throughout the day i manually highlight the vertical row (eg Period 1) and colour it out grey to show its unavailable to enter request in. My question is; is there a way to assign a time stamp to each assigned cells or rows so when that time has passed it automatically colours out the appropriate cells? so this would happen when I'm not able to get to my laptop.

  23. Hi!

    I would like to know if there is a formula that will retrieve the current date after new entry data on a row. I noticed that =TODAY() will change previous dates to the current one. Please advise and thank you!

    • Hi Carlos,

      I'm afraid TODAY is a volatile function. It recalculates itself in all cells each time anything is edited on the sheet. There are a few workarounds I can offer:

      1. You can manually turn new TODAY formulas into values. This way, you will prevent previous dates from changing.
      2. Or you can manually enter the current date by pressing Ctrl + ; on your keyboard.
      3. Or use scripts to solve the task. But since we don't help with scripts, you may try to find the solution here – an overview of Google Apps Script with a lot of helpful content and links: https://developers.google.com/apps-script/overview

  24. I collect requests via a form and results are populated into a response sheet. I would love to be able to have the request date one that I could simply click on and it would take me to the calendar with some of the information. Akin to clicking on a date in an email and it allows you to create an event with select info auto populated but at the least- it takes you to the cal and to the date and time. Can sheets not do this?

  25. I enter into Google Sheets a day and a month 15/12 and it automatically enters it in as 15/12/2022
    As I am entering projected dates we are starting to enter dates for Jan but it puts a 2022, is there any way when i type 15/1 it will add 2023 for a future date rather than automatically do 2022

  26. Hi! This is very helpful! Thanks! I'm new to GSheets. I wonder if you could help me with this. So my Google Forms response populates in a google sheet file. Every time I get a new response, the date and time gets recorded in Column A. I want to copy ONLY the DATES in column A to another column (say, column N). But I want the date to be automatically copied to column N every time a new response is recorded. So I don't have to manually do it. How can I do that? Does anyone know how? Many thanks!

    • Hi!

      Thank you for your nice feedback :)

      You will find ways to extract date unit or time unit from a date time cell in this blog post.

      However, please keep in mind that new answers from forms are always added as new rows to your Google spreadsheet (rather than to existing empty rows). This is the way form responses are collected. So if you choose formulas to get the date units, you will have to copy them to new rows manually. If you chose the add-on, you will have to run it for all newly added rows as well.

  27. ok so I am trying to create a hours tracking sheet.. the problem I am running into is how we log the hours..
    we log our time as 24/h:100/m. i cant find a way to get sheets to properly calculate the times if the sum is over 24..
    for example: if i start work at 18.00 (6pm) and finish at 04.91(4:54am) i get the correct time worked of 10.91
    but
    if i start work at 19.00 (7pm) and finish at 08.52 (8:31am) i get 15.02 when the correct time i need is 13.02
    =MAX((D7+E7-0.5)-12,0)
    its my understanding (very limited) that in order to use the input of time as a fraction i need to format the cells as numbers with 2 decimal places. how do i go about making work like a 24/h clock when calculating my hours worked in this case

    • Hello Douglas,

      Sorry, I'm unable to reproduce the numbers you mentioned. For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into it and try my best to help.

  28. We haev been running the below script for some time for out logistics department. When they enter the phone number (column 9) and name of the driver (column 10), the time is automatically populated in Column eye, it just stopped working all together and nothing was changed...maybe someone can help:

    {
    var masterCell = masterSheet.getActiveCell();
    if (ss1.getName() == "SHIPPING LOG")
    {
    if (ss1.getRange(r.getRow(),9).getValue() == "")
    {
    if (ss1.getRange(r.getRow(),11).getValue() != "" || ss1.getRange(r.getRow(),12).getValue() != "")
    {
    ss1.getRange('I'+r.getRow()).setValue(time);
    //ss1.getSheetByName(helperSheetName).getRange('I'+r.getRow()).setValue(time);
    //helperCell.setValue(ss.getRange('I'+r.getRow()).setValue(time));
    }
    // helperCell.setValue(newValue);
    }
    else if (ss1.getRange(r.getRow(),11).getValue() == "" && ss1.getRange(r.getRow(),12).getValue() == "")
    {
    ss1.getRange('I'+r.getRow()).setValue('');
    }
    }

  29. Hi, can I customize datetime format without having to create temporary cells?

    To clarify, I have this function which grab the datetime in text format from other sheets.
    =INDEX(
    IMPORTRANGE("https://docs.google.com/spreadsheets/d/123abs", "sheets!B:B"),
    MATCH($A3,IMPORTRANGE("https://docs.google.com/spreadsheets/d/https://docs.google.com/spreadsheets/d/123abs", "sheets!A:A"), 0),
    1
    )

    The output is something like 100/10/10, which is y/m/d format of where I'm from. I know how to create 3 seperate columns to customize my datetime format but could this be done in place?

    • Hi Cookie,

      You don't need to create temporary cells, you just need to select this cell with 100/10/10 and change its format via the Format > Number menu.
      If this doesn't work for you or it's not what you mean, please give me more details (examples maybe) regarding your source data returned by your formula and the desired outcome.

  30. =IF(C9"",NOW(),1) Once the date time is updated with this formula, what should be done so that the time is not updated again?

    • Hello Umesh,

      NOW() is a volatile function meaning it recalculates itself in all cells once anything at all is edited in your spreadsheet. Since there are no other standard formulas for this task, I can only advise you to use scripts. You may try to find the solution here – an overview of Google Apps Script with a lot of helpful content and links:
      https://developers.google.com/apps-script/overview

  31. use date in google take out is 1526662987 - how do you figure that out?

  32. IS THERE ANY WAY TO STAMP THE TIME WHEN THE EVENT HAPPENED WITH REFERENCE TO AN DATE WHEN PULLING STOCK DATA ON REAL TIME?
    I AM IN SEARCH OF AN FUNCTION WHEN BREAK OUT HAPPENED WITH VOLUME AND PRICE BROKEN WITH REFERENCE TO AN STOCK
    PL HELP
    ASIANPAINT 2794.1 1.32 2807 2854 2795 2831 1328308 2845 2778.5 1211975 Vol Brkout Buy 2845 2789 1328605 2658 2613 #N/A

    • Hello Ravindra,

      To stamp the time, you'd normally use the NOW() function. However, it's volatile meaning it recalculates itself in all cells once anything at all is edited in your spreadsheet. Since there are no other standard formulas for this task, I can only advise you to use scripts. You may try to find the solution here – an overview of Google Apps Script with a lot of helpful content and links:
      https://developers.google.com/apps-script/overview

      I wish I could help you better.

  33. Hi,

    Is there a way for me to measure the time elapsed between two different dates. I have a date of 13/03/2021, and I have a cell that has the current date. I want a code that measures the difference between these two dates. Is this possible?

    Thanks,
    Joel

    • Hi Joel,

      Yes, just subtract your date from the current one and format the resulting cell as Number or Duration depending on how you want to see the result. You will find more info about it in this blog post.

  34. Hi,
    I'm new to sheets. I'm wondering how I change the time to fifteen minute increments?

  35. Hello
    I want to enter medical data into a spreadsheet such as Temperature & Blood pressure and have the Date and Time data auto fill. I tried this formula but it kept changing the date and time info when I made the next entry. =if(A2="","",today()) I don't want the date or time to ever change as long as data is present in A2.
    Thanks

    • Hello,

      You see, such functions as TODAY and NOW are volatile: they recalculate themselves in all cells each time anything is edited in the sheet. There are 2 workarounds I can offer:

      1. Since you use TODAY to return the date only, you can turn new formulas into values at the end of each day. This way, you will prevent existing formulas from recalculating the next day.
      2. Use scripts to solve the task. But since we don't help with scripts, you may try to find the solution here – an overview of Google Apps Script with a lot of helpful content and links: https://developers.google.com/apps-script/overview

  36. I'm trying to input a time value in a minutes:seconds format. However, it automatically changes it to hours:minutes:seconds.milliseconds. Is there any way to circumvent this?

    • Hello Gus,

      Looks like your cells are formatted this way. Please try to change the format of your column by following the steps described in the blog post.

  37. why wouldn't google allow data validation for "time" ?

    • Hello Lanalee,

      to use time in data validation, you need to either create an additional column with time units and refer to this column with your Data validation criteria (List from a range), or enter time units directly to the criteria field (List of items) separating them by a comma.

  38. How can I enter a numerical date MM/YY (03/18) into Google Sheets and keep it from converting the YY to the current year (03/20)? Cannot enter dates as text because I want to use DATEIF.

  39. very similar to Justine's question above, i have a column for times on my sheet, and i want them to be in Military time, but i want to input like 0800 or 1450 and it auto format to HH:MM format. and if i do a custom number format like "##:##" it works, for the most part, except for the AM(Morning) times, it will show "8:00" instead of the Proper "08:00". Is there any way to fix this to where it will properly show the first zero?

    • Hello Kelly,

      I'm afraid my answer would be the same. The number format you set simply displays the number as you want, but for Google Sheets it is still a number, not time. You can see that by clicking any such cell: formula bar still shows 1450 as a number.
      If this is enough for your goal and you're not going to calculate these timestamps, you can keep leading zeros by creating a custom number format like this: 00:00

  40. Is it possible to drag down a date and time cell but only change the time by increments of 20 minutes as per below?
    May-18-2020: 08:00:00
    May-18-2020: 08:30:00
    May-18-2020: 09:00:00
    May-18-2020: 09:30:00
    May-18-2020: 10:00:00
    May-18-2020: 10:30:00
    May-18-2020: 11:00:00
    May-18-2020: 11:30:00
    May-18-2020: 12:00:00
    May-18-2020: 12:30:00
    May-18-2020: 13:00:00
    May-18-2020: 13:30:00
    May-18-2020: 14:00:00
    May-18-2020: 14:30:00
    May-18-2020: 15:00:00
    May-18-2020: 15:30:00

    • Hello Luis,

      Yes, it's possible. Fill the first two cells, but make sure they are of the format Google Sheets understands. I'd advise you to use the Custom date and time formats for that. Please refer to the end of the first paragraph above for details.
      Once you enter the first two dates and times, select both cells and then drag them down. Google Sheets will add 30 minutes in each cell automatically.

  41. Creating a User Friendly time sheet that is flexible based upon input.
    I almost have everything perfect except.
    Is there a way based upon if input was a date and time input vs a decimal input for the same cell.
    ex 4/10/2020 7:30:00 or 7:30:00 or 7.5
    So I want to give the cell a format for "date and time" OR "decimal" input based upon the input. Is this possible?

    • Hello Brett,

      Once you enter the value, Google immediately recognizes it as Date time, Duration, or Number respectively. However, if you always want to see a tick next to the corresponding format in the Format menu, I'm afraid you'll have to do that manually.

  42. I am trying to make a over time calculation using the formula
    =F6-TIME(8,0,0) +(8>F6)
    Where F6 has the duration of the workday.
    However i get results that are not working so something is wrong.
    Eg if the worktime is 0 the result will show 16:00 (24h-8h)
    Can anyone point me in the right direction?

    • Hello Henrik,

      I'm sorry, I don't understand why you add (8>F6). If this condition is true, "1" will be added, if not true - "0" will be added.
      Please try to explain how your over time should be calculated and I'll try to provide you with a correct formula.

  43. Is there an easy way in Sheets to set up a cell for data entry of time so that rather than having to type in 14:54 I can just type in 1454 and the cell will be formatted to 14:54. looking to save data entry stokes.

    • Hello Justine,

      I'm afraid no. These characters are essential for Google Sheets to distinguish between numbers and other formats and it's the simplest way.
      I should mention that there's a number behind each time unit. For example, 14:54 stands for 0.620833333333333 for Google Sheets. You could enter numbers like this and then format them as time, but you can hardly call it 'easy', I'm afraid, since you should know exactly what to enter.
      So entering 14:54 is the simplest way there is.

  44. Is there a way to change the value of the column to subtract 2 hours from the time entered? Say I enter 15:00 can I have it show 13:00 instead?

    • Henry,

      in one and the same cell - no.

      You'll need a formula to subtract 2 hours from your time. For example, you can enter the time to A2 and put the formula below to B2:
      =A2-TIME(2,0,0)

      Whatever time you enter to A2, B2 will always subtract two hours from it and will show you the result.

  45. Hello, talking about time... I'm new to sheets, coming from excel and still adapting to things I thought were basic and sheets doesnt' do.
    I'm trying to write the time manually in a cell, wich by the way is formatted appropriately, and I have to write 8:00 or 8:01, in excel I could just type 8: or 8:1 and because the cell is time formatted it would understand it. Is there a way to do this in sheets or quicker way to manually write time?

    • Hello Emanuel,

      If you enter simply 8:1, Google won't recognize it since it can be 8:10 as well as 8:01. Thus, I'm afraid in Google Sheets you should indicate the time in a way so spreadsheet understands it correctly: with two digits after the colon. This way the time format will be applied automatically.

  46. I think this article would have solved my problem - but I didn.t stop toread it. The moving bar across the bottom is too distracting.
    This is worse than auto-play videos - at least you can mute those.

    • Thank you for your feedback, Flip.

      You can make the bar much smaller by clicking on the corresponding button at its upper right corner.
      I will pass your feedback to our team so they could take it into account.

      • agree, the moving bar is distracting. It would be better if it moved when hovering mouse over it rather

  47. Hi Sage,
    Below is the script you provided . . . was wondering if you could add another line of code that would set a rule to fix the time range from 8 am to 4 pm on a work day. In event the time data entered falls outside that time range, it would be default set for the next work day at 8 am. Is that possible?

    Thank you in advance,
    Bruce
    _________________________________________________
    function formatColCD_ToDateTime() {
    // First select columns of interest (C&D in this script) and choose
    // Data → Data Validation → Criteria = Date, is valid date.
    // This script then: sets the format to be date and time for the range C:D
    // after a date is picked in those columns.
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getSheetByName('tblInput');
    var c = ss.getActiveCell();
    if( c.getColumn() == 3 || c.getColumn() == 4)
    {
    sheet.getRange('C:D').activate();
    sheet.getActiveRangeList().setNumberFormat('M/d/yyyy H:mm:ss');
    }
    }

  48. HELLO SAGE ,

    i was trying your script code in my personal sheet , but after run process completed , it show up some error alike ""Please select an active sheet first. (line 5, file "Code")".
    So my question is , how can i add active sheet in that code itself . kindly help me to solve this issue .

  49. I created a script that lets you choose a date from a dropdown, and then auto changes the format to include time. Pasted below:

    function formatColCD_ToDateTime() {
    // First select columns of interest (C&D in this script) and choose
    // Data → Data Validation → Criteria = Date, is valid date.
    // This script then: sets the format to be date and time for the range C:D
    // after a date is picked in those columns.
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getSheetByName('tblInput');
    var c = ss.getActiveCell();
    if( c.getColumn() == 3 || c.getColumn() == 4)
    {
    sheet.getRange('C:D').activate();
    sheet.getActiveRangeList().setNumberFormat('M/d/yyyy H:mm:ss');
    }
    }

    • Update: to paste that code click "Tools" --> "Script editor" --> paste code in. ... NOTE - you will have to change the text "tblInput" in the script to whatever your tab is named that you want the script to work on.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)