Pages

9/1/17

How to add a custom time to cells with dates already in them

How to add a custom time to cells with dates already in them. This is a Google Sheets issue I hadn't been able to solve.

I would like to retain my current dates, but add a custom time next to each date - not just add a time format.


When I select the whole cell and go to Format>Number>More formats>More date and time formats... and select the m/d/yyyy h/mm/ss format, the time automatically goes to 0:00:00, but I want to be able change all of the cells to the same and specific time, say 06:30:00. 
Note that if I add a custom time directly into the box above, such as "m/d/yyyy 6:30:00", the "6:30:00" shows in the cells, but is not read as time, but as regular text. Here is what I mean. Notice that the fx section omits the time part and only shows the day, even though it appears to be present in the cells.

I can edit each cell manually, and it will work, but I want know how to edit all cells simultaneously.


ANSWER:
I understand that you'd like to retain the dates and add a specific time to those datesIn order to do this you may need to add a helper column to your existing data (in this case add a column beside the date column and name it as time). Add time 6:30 to one of the cells and drag that cell down to get the same time across the entire column. 

In the third column, write the following formula and change the format of this column to Date and Time:

=ARRAYFORMULA(A:A+B:B)


This should give you the existing date and the time as 6:30 for all the listed dates.