Auto fill dates

JonEric Eubanks

I just uploaded a excel sheet into smarthsheet that was tracking the date when data was inputted since 3-23-20. I created a new form that the user didn't want the date to appear on and a column with auto fill date when created. This doesn't really work with old inputted data bc you cant edit the auto fill cells .

So the issue i am running into is... on a date column i have the function =TODAY() on the two top cells so when a form is filled out it auto fills the date for today. But on the next day it automatically updates dates from yesterday to today causing. Is there something else i need to do in this function?

rgochee

@JonEric Eubanks,

Instead of the =left in the Date column use =[Date 4/7/20]@row this will keep it as a "Date" vs. "Text" string.

Then you should be able to sort and countifs based on the Date Column.

The reason I suggested the =left is the time stamp although says 04/12/20 8:22 pm, with the =[Created Date]@row returns 04/13/20. Which is assume that is due to system time zones

Robert Gochee

JonEric,

Can you just use a Date Created Column then on a Secondary Date Column use =left([Created Date]@row,8). on the form hide the Date Column, and on the Sheet hide the Created Date Column

on the form hide the Date Column,

on the Sheet hide the Created Date Column

Rob

JonEric Eubanks

Thanks for the input I have tried the =left function but it does recognize the dates when being used in a countifs function. I even tried to used the countifs function on the auto fill date created column but it does not recognize all of the dates for some reason. I was going to put this in my initial comment but didn't want to confuse my question anymore.

Andrée Starå

@JonEric Eubanks

Hi JonEric,

Can't you use the System Column, Created? You wouldn't need to use the TODAY function at all, as I understand your need.

Make sense? Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

JonEric Eubanks

Sorry for the confusion that's what i was referring to, i used the  System Column Created date but the COUNTIFS are not working properly...I used the Countif to find dates for today and it's counting 6 cells for today when there are only 2. I also did a filter to the date.

Picture 1 is the Countif function cell and results with 6 cells counted

Picture 2 is the Countif range on the System Column Created that shows only two cells with todays date

Picture 3 are Actual cells in the column with today's date

Picture 4 is a filter on System Column Created that shows only 2 cells with todays date which conflicts with picture ones function.

Picture 5 and 6 are filters used on the System Column Created so there is something off with it.

rgochee

@JonEric Eubanks,

Instead of the =left in the Date column use =[Date 4/7/20]@row this will keep it as a "Date" vs. "Text" string.

Then you should be able to sort and countifs based on the Date Column.

The reason I suggested the =left is the time stamp although says 04/12/20 8:22 pm, with the =[Created Date]@row returns 04/13/20. Which is assume that is due to system time zones

JonEric Eubanks

I used the =[Date 4/7/20]@row but now it's off by a day? Any solution?

a day again is there a way to fix this?

Andrée Starå

@JonEric Eubanks

Did you get it working?

I've seen similar issues before. Let me know if you haven't solved and I'll see if I can find it in the archives.

One solution I've used for some of my clients is the 3rd party service, Zapier. Is that an option?