Date updating to Next Day using Index, Match, Max, Collect Formula

Amy Arnold

Hi,

I'm working with two Smartsheets.

(1) One sheet collects the data from a Smartsheet form. I added two columns to this sheet using the Auto-Number/System Feature (under Edit Column Properties). This features stamps new row entries with a date/time (Created) and by whom (Created By). I'm curious about the cell surrounded by the orange box in the below image.

(2) Second sheet has cell links that pulls the most recent entries by date (Created) and by Name. In the Created column I have this formula:

=INDEX({Created Range 2}, MATCH(MAX(COLLECT({Created Range 2}, {Name Range 3}, "Named Site")), {Created Range 2}, 0))

My question: When the second sheet updates the cell link using the formula above, the date advances to the next day. What causes this behavior?

I am using the formula below in the cells above and below the cell I am having issues with. The formula below is working as intended in the other cells.

=INDEX({Created Range 2}, MATCH(MAX(COLLECT({Created Range 2}, {Name Range 3}, "Named Site")), {Created Range 2}, 0))

Thank you in advance.

Debbie Sawyer

Hello

I have set up a couple of test sheets to see if I can replicate this issue. I am going to pop some data in over the next few hours and tomorrow too; to see if I can see any correlation to time.

I'll pop back in here again tomorrow to see if I found anything. (your formula here worked fine on my initial data)

Kind regards

​Debbie Sawyer Consultant & Training Manager

 

Eric M Oliveira

Hi Amy,

Happy to help, I understand you're experiencing issues referencing a timestamp-based off of the System Generated Created(Date) column, I'd be glad to assist you! On the Smartsheet server-side, we store all dates and times in UTC. In the App though, we surface to the user based on your timezone. We pass you what our server says is the time and then your Smartsheet instance interprets that time per your Personal Settings. So anything row created after a certain time will show via a formula as the next day. 

You may want to compare your time zone to UTC time and see what time your time zone differs. You could then take the data obtained from your research and alter the formula to account for this where if the day values don't match minus 1.

The MID function may help to achieve this as outlined here: https://help.smartsheet.com/function/mid

Have a wonderful day.

Cheers,

Eric

Smartsheet Technical Support

Debbie Sawyer

Thanks Eric

I was thinking that there was a time issue as the one that was showing a day later was after 6pm in the users timezone. Your answer is great :)

Thank you

Kind regards

Debbie

Robin Poppsinger

I have encountered this same problem but for me the date rolls over at 4pm local time. Is there a way to submit this for improvement?