Referencing date and time from another sheet
I am trying to reference an automated creation column from sheet A to populate date AND time in a column in sheet B. I have been successful in the reference, but then have to format the receiving column to date format due to invalid data type. Problem is I need the time to populate as well.
Also, when writing this formula this morning, many of the columns having a creation date of 8/6/20 on sheet A are populating as 8/7/20 in sheet B.
Best Answer
-
To get the date and time pulled over, you can enter your formula into a text/number type column and add +"" (plus quote quote) to the end which will convert it to a text string.
=original_formula
=original_formula + ""
The dates not matching sounds like it is going to end up being a time zone thing for which there are a few different solutions floating about (that we may not even need if we can get your date/time to pull over the way you want to in the first place.
Answers
-
To get the date and time pulled over, you can enter your formula into a text/number type column and add +"" (plus quote quote) to the end which will convert it to a text string.
=original_formula
=original_formula + ""
The dates not matching sounds like it is going to end up being a time zone thing for which there are a few different solutions floating about (that we may not even need if we can get your date/time to pull over the way you want to in the first place.
-
That worked for the formula! Thank you!
Another question. Is there a way to conditionally format for a time greater than 12 hours before the referenced time?
-
I'm sorry, is there a way to conditionally format and highlight the referenced time so that for the times greater than 12 hours ago
-
Hi @Emerus CCC
Smartsheet doesn't currently have a Timestamp function so depending on how your sheet is set up you may not be able to set up Conditional Formatting on this column (as it won't be able to recognize what time it is now and compare that to the text present in the cell). Please submit an Enhancement Request when you have a minute!
You could turn on Highlight Changes if you wanted to highlight certain cells from specific points in time ... see here for more information: https://help.smartsheet.com/articles/521974-highlight-changes-made-to-a-sheet
If this didn't help, it may be useful to see screen captures of your sheet (but please block out any sensitive data!)
Cheers,
Genevieve
-
You could use a "helper column" to pull the time and convert it to a numeric value then run your conditional formatting off of the "helper column".
Help Article Resources
Categories
Check out the Formula Handbook template!
