Edit a timestamp date time

[Hyman_van_Zyl]

Hey all,
Is it possible to edit the date/time in a timestamp column? It does not seem that way. Is there any other way to set a date based on a value change in another column, but that the date can also be edited? Without using an action to change the value.
Thanks
Hyman

[Kirk_Masden]

I’m trying to image a situation in which on might want to do this. Depending on what you are trying to do, I would think that it might be possible to either use a virtual column to do it or, if you need to write to the sheet, to make one more column that you write to with and action – though I know you want to avoid using an action.

Maybe someone else will be able to provide a good answer. If you don’t get one soon, though, perhaps it would help if you could explain the reason why you need to change the value.

[Hyman_van_Zyl]

Hey Kirk,
Thanks for feedback. So you confirm that it is not possible to edit a timestamp column?

[Kirk_Masden]

I know that you can’t put an expression in such a column. I haven’t tried overwriting one with an action though.

[Aleksi]

@Hyman_van_Zyl Would you please open your case a little deeper, thanks.

[Hyman_van_Zyl]

Hallo Aleksi,
So I made a change timestamp column called Date Done linked to the “tasks done” column if value are set to done. So when I change the value of the task done column to “Done” a timestamp are created in the Date Done column. But now I find that it is needed to sometimes change the date that the task was set to Done. I see you can set the Date Done column properties that it could be edited but it does not seem to be editable. It behaves more like a virtual column.
Is this the case that a change timestamp column cannot be edited?

[Steve]

Hyman_van_Zyl:

So I made a change timestamp column called Date Done linked to the “tasks done” column if value are set to done. So when I change the value of the task done column to “Done” a timestamp are created in the Date Done column. But now I find that it is needed to sometimes change the date that the task was set to Done.

Try this for Date Done:

1. Change the column type to DateTime.

2. Set Initial value to:

   IF(
     AND(
       ("Done" = [tasks done])
       ("Done" <> LOOKUP([_THISROW].[row-key], "MyTable", "row-key", "tasks done")),
     )
     NOW(),
     [Date Done]
   )
   

   replacing MyTable with the table’s name and row-key with the name of the table’s key column.

3. Set Reset on edit? to ("Done" = [tasks done]).

[cessnafrrg]

Dealing with a similar challenge here. My app needs task start/end/duration value, So I have [Start Task] and [End Task] buttons (enum type) a few tabs apart in my multi-tab form that walks users step-by-step through the task. The [Start Time] and [End Time] columns are set as ChageTimestamp type monitoring those 2 buttons.. But the issue is, people sometime forgets to click that button on time or bypass the form altogather when doing their tasks and tries to fill out the form later to submit their hours, and start/end time columns being ChateTimeStamp type, doesn't allow them to set their own times. I tried changing their types to regular DateTime, but then I lose the option of auto populate the times when someone is using the workflow properly using the Form. Any ideas?

[Steve]

Perhaps rather than using Enum buttons and ChangeTimestamp column type, use a DateTime column type and Action buttons. The columns would remain editable manually, and the buttons can be used to set them as well.

[cessnafrrg]

Hi Steve. The workflow and all the step by step checklists are now defined in a multi tab form. Is it possible to insert action buttons in a form? 

[Steve]

Nope.

[cessnafrrg]

Thanks Steve.

I tried using actions to to overwrite the ChageTimestamp values from outside events (outside the form), but it turns out that actions cannot update values for any ChangeTimestamp field or any field with a formula. 

I was finally able to implement the following work around:

1. For every ChangeTimestamp field I also created a DateTime field and set its initial value to come from the corresponding ChateTimestamp field.

2. Created a Enum button for "Manual Entry/Overwrite" to act as a state variable.

3. Show the ChageTimestamp fields when Manual Entry button is not active and show the Datetime fields when manual entry is active.

4. All in-form App Formulas that were using the ChageTimestamp values had to be updated to read data either from ChageTimestamp or Datetime field based on the manual entry flag.

This worked fine until someone saved the the form in the middle of the workflow, at which point when he reopens the forms to continue with the workflow, all the remaining Datetime fields would stop reading the initial value from the corresponding ChageTimestamp fields. This was handled by:

5. Adding an action that executes on form save. In that action I am copying data over from ChangeTimestamp to DateTime fields if the Manual Entry is inactive. 

[Aleksi]

Yes that’s true… you are not able to modify this ChangeTimestamp field.

[Hyman_van_Zyl]

Ok thanks, just wanted to make sure about that. Will have to use actiona then.
Greets
Hyman

[Hyman_van_Zyl]

Thanks Steve, will try it