How To Format Date In Google Sheets : Simple Step

By /

Google sheet now become one of the most popular data management tools in the industry. it’s now more advance and improves collaborating working features to deal with the same sheet among many people. Date & time is one of the key features in google sheet & it has a lot of features and features as well. in this tutorial we cover how to format date in google sheets in google sheet using the inbuilt google option and custom data validation.

The basic date formula mostly using to format date in google sheets

In google sheet contain over 20 ways to define format date in google sheets. this is most common usage of date format in google sheets

  1. DATE(year, month, day):  this is the primary date function/date format in the google sheet. This ate need to be Number w& separate by a comma,
    =DATE(1969,7,20)
    =DATE(A2,B2,C2)    Inputs to DATE must be numbers & Year (yyyy) contain a four-digit number. month (mm) & day (dd) contain two-digit number.
  2. DAY(date):  this is the primary date function in the google sheet. This ate need to be Number w& separate by a comma,
    =DAY(DATE(1969,7,20))
    =DAY(A2)
    =DAY(40909)
    =DAY("7/20/1969")
    This is will important when you need to define the day in date on cell reference.
  3. TODAY() : TODAY provide day without time format in the cell reference. But remember that this function can hurt your spreadsheet performance. Because it always needs to verify the current date in the spreadsheet.
    =TODAY()
  4. NOW(): this can be used to get the current date and time as a data value into a spreadsheet. But make sure that this function really hurt for spreadsheet performance same as TODAY() function.
    =NOW()
  5. DAYS(end_date,start_date): this function can be used to validate the date difference between two dates.
    =DAYS("7/24/1969", "7/16/1969")

How to insert date picker/calendar in google sheets?

Instead of using manual date entering, google sheet offers a special data validation feature for date popup which can be insert date without manual entering. it extremely easy to format date in google sheets.

  1. Open a spreadsheet in Google Sheets.
  2. Highlight cell or column you’d like to add a date picker.The second dropdown will represent how to date format needs organize in your selected cell or column. I select is a valid date. Please read below full detailed instructions on these option usages.
  4. Then click the save button to apply this date validation to your selected area.
  5. Then double click the cell (which you already apply data validation) & the date picker will pop up into the relevant cell. 

    Feature of date criteria in data validation (to format date in google sheets).

    Data validation is represented a set of criteria parameter that implements with cell reference without any formula. In data validation set up certain parameter to deal with the data operation.

    How to Change the default date format in Google Sheets?

    Most of the time you may need to deal with multiple region base project or data. You have to change the date and time base on that region format. There is 2 way you can implement date format setting into spreadsheets.

    #Way 01: change local settings

    You can change the spreadsheet location setting. but it not recommended way if you working with multiple region datasheets.

    #Way 02: define the custom date and time formats

    This is recommended way to define the date and time value that is required in a parsing operation. Let’s check how t implement it with google sheet.

    1. Open a spreadsheet in Google Sheets.
    2. Highlight cell or column you’d like to add a date picker, or which already add. Don’t worry about the column header (it will automatically reject).Then move your mouse pointer to the top of navigation and click Data ( middle of Format and Tool navigation link ) Then Select Format > Number > More Formats >  More date and time formats.Then you can get custom date formats pre-define dialog box. You can scroll to define date preset on the option box or you can create a custom date format using the custom date set upfield.
      For this example, I convert my current date format to Australian date format ( DD/M/YYYY = 14 March 2016 )

      1. Click the currently selected date, month year in the setting field and click the delete button to remove the existing selected parameter.Remove separator symbol if not use (select and press delete/backspace button)
      2. Click the arrow down icon on the right side in the setting field.Select year, month, day base on your date format & insert a suitable separator symbol after all Click apply button.your custom date format will update all selected date records.

    By default, the Google Sheet date start  1/1/1900. Because google sheets use the default date system as 1900.

    Conclusion

    Google sheets are one great tool for organize, edit, analysis variant type of information. Date in one of the important data references most of the time using in the spreadsheet. Use need an easy way to operate ( without query writing ) in the spreadsheet. Today we discussed what type of date formatting features/formulas which you can easily implement with google spreadsheets.

    Related posts:

    1. How to Make Multiple Selection in Drop-down Lists in Google Sheets
    2. 5 Easy Ways To Create A Pivot Table In Google Sheets (With Example)
    3. Conditional Formatting in Google Sheets ( 6 Useful Examples )
    4. How to add checkbox in google sheets ( 7 Useful Example )
    5. Right Way To Use Google Sheets IF Function (6 Helpful Tips)
    6. Top 7 Google Sheets Formulas Every Beginner’s Should Know

Related Posts

Scroll to Top