Referencing date and time from another sheet

Emerus CCC

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.

Paul Newcome

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.

Paul Newcome

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.

Emerus CCC

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?

Emerus CCC

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

Genevieve P.

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

Paul Newcome

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".