Q&A for work. Currency > Convert an Amount between Currencies. When a range with both width and height greater than 1 is specified, cell values are appended across rows rather than down columns. Open a sheet with data, and click right beside the cell where you want to insert a merged column. Converts a provided date/time, percentage, currency or other formatted numeric value to. When building different formulas, sooner or later you will notice that not all of them recognize dates entered as they are: 12/8/2019. Search. - Clear. Type a formula to merge the first cells in each column (most likely, these will be cells from row 2 unless your dataset doesn’t have a header row). =IMPORTRANGE ("1lIpak48SxPpi4aWo20_BuquZ12zCQXekwSCEyBcYotQ";"Dia1!A1") Now, I want to make the function IMPORTRANGE as a concatenation with the aim to make it parameterizable. 1. So, all you need to do to make the formula work with the other cells is drag it downward. The values in the date columns always equal the price multiplied by quantity, and each row only has an entry in a single date column. Using these in CONCATENATE, Google Sheets lets me combine date and time in the desired format with other characters or text: =CONCATENATE(TEXT(B2,"D MMM YYYY"),", ",TEXT(C2,"HH:MM:SS")) Combine columns in Google Sheets. 1. =CONCATENATE(TEXT('Concat Sheet Data'!T9:AD, "DD-MM-YYYY")) Example: 2/feb/2020,3/feb/2021 I tried the above, but doesn't function. Hope that helps!1. , A7,B7). Google Sheets - How to concatenate values from different rows and columns if the cells meet certain criteria. Book Free Live Demo. – Go to Data > Data Tools. How to Combine the Text of Cells in Google Sheets? To combine the contents of different cells without merging them, you can use the CONCATENATE function, the “&” operator, or the JOIN function. Example A B; 1: Data : 2: Hello : 3: there : 4 : 5. ; Next, type the equal sign ‘ = ‘ to begin the function and then follow it with the name of the function, which is our ‘ textjoin ‘ (or ‘ TEXTJOIN ‘, not case sensitive like our. return firstName + ” ” + lastName; } Save the script with a name of your choice. Limitations of standard functions used in the formula. 3 quickest ways to merge multiple Google sheets. WORKDAY: The date n working days from a date. Enter this formula: =concatenate(text(A2,"mm/dd/yyyy")&" "&text(B2,"hh:mm:ss")) into a blank cell where you want to put the combined result, then press Enter key to get the first combined cell. CONCATENATE is an "aggregating" function (like SUM, AVERAGE etc) that will "gobble up" and process everything within the parentheses. Run the collection from Add-ons > Power Tools > Start and click the Formulas icon: Tip. The above steps would merge the three cells and make them one. To get the data source file for this lesson open. CONCATENATE function; 8 of 43. Next, type =IMPORTRANGE in the cell (you. Register To. Pro tip: Use apostrophe at the start of a formula to turn it into a text string, which won’t execute. Google Sheets > Perform a Function. The most common use of this function is to combine a series of words in di. Do a search on "W3" and "JavaScript". Next, highlight the values in the range A2:B9. CONCATENATE text strings and numbers in Google SheetsThere are three ways to concatenate values for the IF condition in Google Sheets. CHAR; 5 of 43. How to add number of years months and days to date in Google sheets? DATE YEAR MONTH DAY EDATE: How to add or subtract specific years,. Then again, use the fill handle for the remaining cells. Get the result. Learn everything about Ablebits add-ins for Microsoft Excel, Microsoft Outlook, Google Sheets and Google Docs. Google Docs Editors. 0. Need more help? Try these next steps: Post to the help community Get answers from community members . The array_formula parameter can be: A range. Applying Custom Sort Option to Sort Date & Time in Excel. Select both columns you want to merge: click on B1, press Shift + Right Arrrow to select C1, then press Ctrl + Shift + Down Arrow to select all the cells with data in two columns. Notes. Function: DAYS Description: Returns the number of days between the two dates Example: DAYS({end date column name}, {start date column name}) => diff between dates in days. pnuts. Select the desired date type. Merge Google sheets & add related columns using VLOOKUP. Incorporate dates and times into the behavior of your app. e. You can use the following formulas to combine a date and time in separate cells into one cell in Google Sheets: Method 1: Use Addition = A2 + B2. Another way for Google Sheets to convert date to number is by using the DATEVALUE function: =DATEVALUE (date_string) where date_string represents any date in the known for spreadsheets format. Or, drag and select a range. CONCATENATE ("Welcome", " ", "to", " ", "Sheets!") CONCATENATE (A1,A2,A3). Step 2: Drag the formula applied to cell C2 till cell C5, then we will get the results for the remaining data. Click the “Add files” button to add new spreadsheets to the add-on. Select the cell and enter the formula. Let’s begin with a simple task: There is a Google Sheets doc with two sheets: Invoices 2019 and Invoices 2020. It benefits people who wish to display the value in a cell in a specific format. The CONCATENATE function allows you to link multiple strings together in Excel. When the Custom Date and Time Formats window opens, you'll see the editing area at the top with. Hey! Thanks for reply to my question! Your solution does work! However, I notice when I add empty columns to the left of column A, and push all of the old data towards the right, the formula changes to sequence of data, and. Here, as you can see, the number has lost its. A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. I have been successful in using concatenate: =ArrayFormula (concatenate (C3:F&", ")) but there are extra commas that don't need to be there in between some data and a lot of extra commas at the end. I have worked with this and did not have any issues. ASC function; 4 of 43. IS_SAME({Date 1}, {Date 2}, 'hour') => 0. You may also add spaces by adding two quotation marks as a parameter and a space in between. Select the sheets you want to. Suggestions appear above the keyboard as you type, so you don't have to enter the entire word. 2. Here are two Time-related Text formulas to make you understand how to format time in Google Sheets. Tick off those columns you want to get as a result: both date and time or only one of them to extract from the column. GOOGLEFINANCE; 5 of 10. Step 1: Click on the cell where you want to create the new date and enter the formula below in the selected cell. 1. Now see the below CONCATENATE formula. Press = (the equal sign). result_range: The range to consider for the result. 4. That's how Excel stores dates. Let's get combining! Method #1 – Using TEXT Function and '&' Operator. eventDate + " " + eventObject. Method #1 – Adding the number 0 to the existing formula. Drag it all the way to the end of the column and it will apply the same shortcut. Compares two dates up to a unit and determines whether they are identical. 55”. Copy the date (CTRL + C), then paste only the values using Paste Special or Shortcut CTRL + ALT + V. It's from an earlier iteration, so it's not 100% exact with what I said above, but the goal is still the same. CODE; 7 of 43. Schedule your function to auto-run. If you prefer the native options to combine sheets in Google Sheets, then let’s check them out below. But the only date format I have used was the long date/time format in the google sheets (e. WORKDAY. This time, you need to place a reference to a cell with the existing record first, then append the new text: =A2&", US". which is undesirable because they may cause side effects such as converting the text string 1 2 3 to the date 2 January 2003. For example, you might want to get the current quarter based on the current date and time. Google Sheets - Concatenate 2 rows across columns into 1 cell. For information about using text expressions in templates, see Text expressions. [UPDATED]: To create a google calendar event using AppsScript we can use the simple method of createEvent(eventName, startTime, endTime). Figure 2. Result. Now we use the IMPORTRANGE function. Note. Select key columns with duplicates. In fact, extra spaces are so common that Google Sheets has a special Trim tool to remove all whitespaces. Method #1 – Adding the number 0 to the existing formula. To get started with Google Sheets add-ons, open a new or existing spreadsheet in your browser, select the Extensions menu in the top-right navigation, hover over Add-ons, and click Get Add-ons. 3. This way, you can use UNIQUE and SUM together in Google Sheets. Criteria_range1 (required) - the range of cells to be evaluated by criterion1. ENCONTRARB (FINDB) 12 de 43. Insert a new column in Excel. Column Q ("Status") is the current Status and is populated by a dropdown. Step 1: Select your main table. Your formula may contain:The TEXT function in Sheets allows you to change a number into text. up to 30 total. Type the equal sign ( = ), then type CONCATENATE . 1. =query ( {junesheet!A2:H5;julysheet!A2:H5},"Select * where Col1 is not null ") The formula is for our sample data above. Here's a short video to learn how to combine Date and Text in Google Sheets - without getting the numbe. Open the VBA editor by pressing ALT+F11 or Option + F11 on Mac. Related pages. This function may be used to format a value of any type as Text. Notes. format - Optional. And it shows the current time. Get insights together with secure sharing in real-time and from any device. You can use the TIME function to add or subtract up to 60 seconds: =Start time + TIME (0, 0, N seconds) For example, add 30 seconds: =A2+TIME (0,0,30) Or substitute 30 seconds: =A2-TIME (0,0,30) To calculate over 60 seconds, use simple maths:PERKS OF COMBINE SHEETS • Combine as many spreadsheets as you need • Include extra sheets to the existing result at any time • Add sheets from Google Drive using the quick search field • Pull data from the entire sheet or adjust the range manually • Keep the formatting of the original datasets • Put the result to a new. Related Tutorials: Unstack Data to Group in Google Sheets Using Formula. After pressing Enter and auto-filling the other cells in that column, we’ll see the concatenated dates and times at once. sum(B2:B5+C2:C5+D2:D5) ) Now, I am adding a new range for Product A1. Merge Google sheets, update cells & add columns using QUERY. That is, CONCATENATE(A2:B7) is equivalent to CONCATENATE(A2,B2,A3,B3. Wrap the formula in ARRAYFORMULA, editing the data ranges to cover the entire columns. Google Sheets function list; 2 of 43. Right-click the mouse and choose Insert. The CONCAT function doesn’t only combine text. With Start. 1. For example, using =“ABC”&NOW () will give us the output as ABC14/09/2019. Below is the Query formula to combine the above two Sheets’ data into a single sheet. (Google Sheet) Combine text and Date in Cell. By default, Excel does not display. Automatically Generate Charts With Google. CONCATENATE: Appends strings to one another. Enter the CONCATENATE function, including the cells you want to combine. Don’t forget. You can also do this using the Ctrl+B, Ctrl+I, Ctrl+U, and Alt+Shift+5 keyboard shortcuts, respectively. Here are some common mistakes to avoid when using the CONCAT function in Google Sheets: If you prefer the native options to combine sheets in Google Sheets, then let’s check them out below. COMBINE DATE AND TIME — GOOGLE SHEETS FORMULA AND EXAMPLE. 25 day). To start with, I merge cells in those rows via Format > Merge cells and then count the sum for column E:Take the following steps to combine columns horizontally in Google Sheets using the concatenate function. To combine text, in a cell or. A new CSV file or an existing CSV file. Select the cell where you want the date and go to Format > Number > Custom Date and Time in the menu. Formulas within the text help getting a neatly looking row with some totals – numbers with their short descriptions. start_date – a date used as a starting point. <cell_id_of_date> and <cell_id_of_time> have to be replaced with their respective cell ids as per your data arrangment. Or concatenate the date and time, and use new Date() to create a new date type variable. For Google Sheets to know you're about to enter a formula rather than a number or text, start entering an equal sign (=) to a cell of interest. Combine several files with a formula. DAYS: Returns the number of days between two dates. Combine multiple columns into one single column; Combine two or more cells and transpose; Combine data from multiple Google Sheets tabs and get sheet/tab name; Others Formulas. Select the file to which you’d like to copy the tab data. 3. To combine columns horizontally in Google Sheets, follow these steps: Type =ARRAYFORMULA ( to begin your formula for combining columns. lookup_range: The range to consider for the search. 50% I have individual charts for each showing trending for database space. Learn how to join text (combine text) in Google Sheets. Google Sheets has some really simple formulas that you can use to combine cells and columns. Help Center. Step 1: Click on the cell where you want to create the new date and enter the formula below in the selected cell. The Impala queries are:This section is talking about how to keep date format in the CONCATENATE function in Excel. Given say 2 different dates in A1 and B1, what I want to do is just show the range of dates as a label in A2 by using =concatenate(A1, " - ", A2) However, A1 is in the default format (MM/DD/YYYY) and I want the label to have dates in the format DD MON, YYYY (as in 12/21/2012 -> 21st Dec, 2012). See Two-argument form notes below. Using Format - Date - More - Year (1930)-Month (05)-Day (01)T:Hour (01):00-0800, I get the proper format for the 1st part of the date range ( 2016-06-15T12:00-0800 ). Learn how to join things together in Google Sheets by using the CONCATENATE, CONCAT and AMPERSAND functions. When using the CONCATENATE function, Google Sheets become automatically convert dates to numeric values. For example, TEXT (12. yyyy" is your date format & tells Google Sheets to combine the two. The word concatenate is just another way of saying "to combine" or "to join together". To use CONCAT, open your Google Sheets spreadsheet and click an empty cell. Each of them were retrieved by . There, click on CONCATENATE to insert the function into your spreadsheet. FREE OF COST. Combine Two Dates using Text Function To write a formula to concatenate two dates, use the following steps: First, use the text function, and in the text, the function refers to cell A1 and uses the format “dd-mmm-yyyy” to get the first date as a text. For example, I want to convert August 10, 2023 to a number. To do this, follow these steps: Click on an empty cell, C3 in the example above. Using these functions can greatly help teachers manage lists of stud. title1 | Aug 08,2019 | 7:30 | 8:25 title2 | Aug 10,2019 | 8:30 | 9:25 I want to grab date and time from google sheet then createEvent in calendarApp. Select (all or specific columns) The very first clause – select – is used to tell what columns you need to return with Google Sheets QUERY from another sheet or table. Here is how to do this: Click on one or more cells that you wish to modify. Once the formula bar is active, you can begin to type in your formula. To set the end time to be 1 hour from the start time, try using the time modifier of +1h instead of +30m. To concatenate strings with space in Google Sheets, you need to use a space delimiter: " ". =ArrayFormula(. 2. 55”. 1. DATETIME (NOW ()) returns the current date & time from a value. A function that returns a result greater than a single cell. Google Sheets formula to combine date and time with dash as a separator in one cell: =concatenate (text (A2,"mm/dd/yyyy")&" - "&text (B2,"hh:mm")) Step 3: Drag the blue. 50% T - 11/2/2023 67. In Javascript date and time are the same objects, there is no time object that has no date and no date without time : their native values are milliseconds counted from January 1 1970 at midnight (which is an integer). Press Enter to apply the. The syntax for the DATE function is: = DATE (year, month, day) . 1. Formula: You can use this Query to concatenate a large number of columns in Google Docs Sheets. Whatever range you indicate, it always scans its first column. ; Combine necessary cells of data together Concatenate records from the selected range into one cell. Use Shortcuts to insert the current date and time 2. How to use Merge Sheets. Sum by month SUMIF cells if contains part of a text string; Sum total sales based on quantity & price; Convert 1-12 to month name; Dynamic current date and time; Count blank cell; Count cell between two values; Count cells that contain numbers and ignore blank Use the formula =D2+E2 in a new column and you get a complete date object directly useable in JavaScript. Concat columns from query result in Google. 18. To do this, follow these steps: Click on an empty cell, C3 in the example above. Select all columns. You can include CONCATENATE () functions or Ampersand ( &) operators in your template to combine two or more values to yield a single text value. Applying CONCAT Function. We can perform Excel Concatenate Date with other values with the help of the Ampersand (&) or the inbuilt Concatenate Function in Excel. If you want to see the current date and time each time you open your Google Sheet, you can use the NOW or TODAY function. If the specified value is not a DateTime, or value, returns a blank. You are reading this tutorial because you are trying to join some fields and strings in google sheets using the CONCATENATE function but when it comes to dates, it's converting them to numbers instead of the. 2. You can add an ArrayFormula to existing functions. evaluate arithmetic expression of string literal. ARABIC; 3 of 43. Copy and paste the following code into the VBA editor (This VBA code creates a function that combines date and time to separate cells into one cell ) Function CombineDateTime (dateCell As Range, timeCell As. So, in our case, the CONCATENATE Google Sheets formula will look like this: =CONCATENATE(A2," ",B2) Google Sheets CONCATENATE multiple cells (more than 2) Let’s move further and. Clear searchI need to concatenate data from multiple rows/columns but skip columns where the value for the column is "None". Merge columns in Google Sheets into one Join text from multiple columns: merge values horizontally. Press Cmd/Ctrl + Shift + Enter to add an ArrayFormula around your function in Google Sheets. =INT (A2) Drag the formula down the column to see the following. The format to be applied to the temporal value (value). To unfreeze, select a row or column. . To begin, select the cells you wish to concatenate. On the Data tab, find the Data Tools group, and click Text to Columns. =QUERY (A2:A, "SELECT QUARTER (A) LABEL QUARTER (A) 'Quarter'") Add the above formula to your sheet to get the quarter values. This tutorial will demonstrate how to concatenate a date with another string of text in Excel and Google Sheets. An extra setting lets you 'reverse all number signs' in the selected range at once. Combine a Date & a Time Column in Google Sheets for a createEvent() function [duplicate] Ask Question Asked 8 years, 4. Time component functions. May I clarify; please correct me. Viewed 857 times 1 I am trying. The Excel concatenate date example formula below: Joins: The string stored in cell B6; and. 55 in cell A1. Share. If you. Billing. 71. 2. (column A in. If you want to combine text from two or more cells into a different cell (concatenate the values), you can do this with a simple formula. With Power Tools, all it takes is to open the Dedupe & Compare group, click Combine Duplicate Rows, and follow 3 steps: Select your data. If you're going to work with dates in electronic tables, Google Sheets DATE function is a must-learn. Combine First and Last Name Example. Ask Question Asked 3 years, 6 months ago. Join Date with Text. I think google sheets is either reading them as blank and giving me an error, or sees there a query'd set of data and it's unable to retrieve the list. Enter this formula: =concatenate (text (A2,mm/dd/yyyy)& &text (B2,hh:mm:ss)) into a blank cell where you want to put the combined result, and then press Enter to get the first combined cell. . Click on Merge cells option. 75. ) In this formula, text1 represents the first cell to be combined, text2 represents the second cell to be combined, and so on. The Google Sheets graph is built, the chart editor is displayed. Upload the document from your documents or via a link from the selected cloud storage space. The second argument is B2 that consists of the last name. How to use the Consolidate Sheets add-on. How to Concatenate in Google Sheets (Combine Cells without Losing Data) Well, spreadsheets offer several tools for this task. =query (A2:B5,"Select A, sum (B) group by A label Sum (B)''")This help content & information General Help Center experience. ”. I need to populate the Column C with all the possible combinations of the values in Columns A and B. The. In our case we will have. Method 2: Use CONCATENATE and TEXT =CONCATENATE(TEXT(A2,"m/d/yyyy"),". =CONCATENATE (CellA,CellB) or. Format a date fetched from another cell for display using CONCATENATE. 3/9/2021 13:30:00). Note: If you use the above data on a future date, it will not work correctly. Here, we want to add our customer's phone number in cell C2 to the end of their. dd. You will see the output as shown above. For example, =CONCATENATE("*", Sheet1!A1, Sheet1!B1), and I want to make the * bolded or blue in color or something else. 3. An example formula might be =CONCAT (A2, " Family"). Things to Remember. DATEVALUE: Converts a date stored as text into a valid date: DAY: Returns the day as a number (1-31). . CONCAT is short for “CONCATENATE” which means that you add one string onto another string. Preserve leading zeros in a Google Sheets CONCATENATE function. 2. Combine Text and Date in Google Doc Spreadsheet. ; Wrap text in the. Month - enter the month as a two-digit number (mm) or. For example, 42, "Cats", or B24. I could find no magic function in google sheet. Step 3: Identify matching columns. Well, spreadsheets offer several tools for this task. But you can concatenate a number without losing its format by using the TEXT function in Google Sheets. Step 3: Select matching columns. It follows that [Date] + [Time] = [Date/Time]. In your Excel spreadsheet, select all of the dates you want to change to text. The NOW function returns the current date and time in the form of a decimal number, where an integer represents the date, and a fractional value represents the time. getActiveSheet(); var startRow = 2; // Firstly row to data to process type83. Choose cell C5 and navigate the cursor to the plus symbol. You’ll be using two TEXT functions here: one to display the date value, and the second one to display the time value. DAYS360: Returns days between 2. Confirm that the cells merge correctly. The syntax for each is NOW() and TODAY() with no required arguments. Enter the Current Date and Time: NOW and TODAY . CONCATENATE ( first_name_cell ," ", last_name_cell) In the first formula, concatenation is done with an ampersand character (&). The date stored in cell B7; While formatting the date (stored in cell B7) as a date (“d mmmm yyyy”) with the following characteristics: The day number, without a leading 0 (d); followed by. CellA. It takes two parameters: The date or time value to be converted. For example, I want to convert August 10, 2023 to a number. Google Sheet - Concatenate text with date format. The MATCH portion of the formula fills the argument for position (row, column). TEXT (number, format) number - The number, date, or time to format. Click on the "More formats" menu in the top toolbar (The button says "123") Click "Plain text". Step 1: Select sheets to consolidate. A behavior that is very odd to me. Generate a webhook and paste it into the Integromat add-on in Google Sheets. AppSheet. When a range with both width and height greater than 1 is specified, cell values are appended across rows rather than down columns. 3. You can either simply add the date and time values and Excel will. startTime);3 quickest ways to merge multiple Google sheets. Click on Module, then select insert. Try your keywords on Google Web Search. CLEAN; 6 of 43. Now I want to add a column that shows the name of a country to the filtered column. 3. That is, CONCATENATE(A2:B7) is equivalent to CONCATENATE(A2,B2,A3,B3,. If you are using your own dataset, please make sure that the table is sorted based on column A. 0 forces display of zeros if a number has fewer digits than the format specifies. 50% T - 11/2/2023 67. In the Sheets UI, you apply number and date formats to cells using the Format > Number menu. This range's row or column size should be the same as the lookup_range, depending on how the lookup is done. Find the “XPath” for the part of the web page you want to scrape. In the “Original Data Type” section, select “Delimited” and click. Press the Merge Cells button on the toolbar. However, you use the TEXT function to specify that the date should remain formatted as a date: =CONCATENATE(A2, TEXT(B2, "M/DD/YYYY")) This particular formula will concatenate the strings in cells A2 and B2 and keep the date in cell B2. In Sheets, 30% will be stored as 0. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also the spaces. But ;@ with Date and _ (@) with Number doesn't makes any difference or scene unless is not been used properly. You can use the following basic formula to concatenate a range of cells with a comma in Google Sheets: =TEXTJOIN(", ", TRUE, A2:C2) This particular example concatenates each cell in the range A2:C2 with a comma in between the characters of each cell. Date component functions. Select the range of cells that you want to concatenate with line break, and then click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, see screenshot: 2. ”. To the right, click the Type menu, then select Date or Date & Time. Here are some common mistakes to avoid when using the CONCAT function in Google Sheets:Format a date fetched from another cell for display using CONCATENATE. To combine date and time in Google Sheets, users can use the CONCATENATE function. To link two or more cells in a basic way (similar to CONCAT), type. Given say 2 different dates in A1 and B1, what I want to do is just show the range of dates as a label in A2 by using =concatenate(A1, " - ", A2) However, A1 is in the default format (MM/DD/YYYY) and I want the label to have dates in the format DD MON, YYYY (as in 12/21/2012 -> 21st Dec, 2012). Inserting CONCATENATE and IF Functions. Then, click the “Data” tab at the top of the page and select “Text to Columns. You can specify multiple parts enclosed in quotes and separated by a comma. First, edit the cell and enter “=” in it. Remove Minutes from Time if Minutes are Zero. The first cell will contain the sum of A1 to A10, the cell to the right will contain the sum of B1. If standard ways of Google spreadsheets to combine data from multiple sheets seem dull, and the functions scare you off, there's an easier approach. in a cell J21 I have entered a numeric value 1. Returns the time from a hour, minute, and second. g. Just select all Google Sheets cells where you want to remove whitespace and choose Data > Trim whitespace in the spreadsheet menu:Step 1. You need to convert the date value to a string of the form yyyymmdd. In the. I currently have the following formula: =ArrayFormula (concatenate (F2:K2 &char (10))) The above formula works, but I have to copy down the formula every time I get a new Google Form entry. However, you may use the following. Click. 11 Ideal Examples to Concatenate Multiple Cells in Google Sheets. Let's see how the ampersand is used to add text or any characters to the end of the cells. Learn more about Teams Create a Custom Date Format. title1 | Aug 08,2019 | 7:30 | 8:25 title2 | Aug 10,2019 | 8:30 | 9:25 I want to grab date and time from google sheet then createEvent in calendarApp. Thus, if you need to merge 2 Google sheets and pull IDs (1st-column data) based on berries (2nd column), VLOOKUP won't help. 10000). ”. Possible duplicate of How to concatenate a date in MS-excel and receive a date (not a number)Lastly, the only real use of the CONCATENATE function is when dealing with enough data that time is being saved versus entering the data manually. This is what your pivot table should look like after going through all the steps. Google Sheets formula to combine date and time with dash as a separator in one cell: =concatenate (text (A2,"mm/dd/yyyy")&" - "&text (B2,"hh:mm")) Step 3: Drag. LAMBDA function; Visit the Learning Center. Need to add the current time or date to your Google Sheets spreadsheet? You can use the NOW or TODAY functions. 2 Answers. =concatenate (text (A2,"mm/dd/yyyy")&" "&text (B2,"hh:mm:ss")) Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum. Using Direct Excel Drop-down Option to Sort Date & Time.