Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Se puede registrar la hora del cambio de una columna?
Buenas, quisiera saber si hay alguna forma en que se pueda registrar la hora en que se realiza el cambio a una celda.
Ejemplo, tengo una columna de estados: Pendiente-Devuelto- Completo, me permite registrar en forma automatica la fecha en que se realiza el cambio de estados, pero necesito que me registre la hora en que se realiza..
A fin de saber cuantos minutos tardaron de cambiar de un estado a otro.
Es posible con alguna formula?
Desde ya, muchas gracias.
Best Answers
-
Hi @CarolLem
I hope you're well and safe!
To add to Paul's excellent advice/answer.
Please have a look at my post below with a method I developed. (you can request a copy if you like)
More info:
Would that work/help?
I hope that helps!
Have a fantastic week & Happy New Year!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
----------------
Google Translate
Hola @CarolLem
¡Espero que estés bien y a salvo!
Para agregar al excelente consejo / respuesta de Paul.
Por favor, eche un vistazo a mi publicación a continuación con un método que desarrollé. (puede solicitar una copia si lo desea)
Más información:
¿Eso funcionaría/ayudaría?
¡Espero que eso ayude!
¡Que tengas una semana fantástica y feliz año nuevo!
Mejor,
Andrea Starå | Consultor de flujo de trabajo / CEO @ WORK BOLD
✅¿Mi(s) publicación(es) ayudó(n) o respondió su pregunta o resolvió su problema? Apoye a la comunidad marcándolo como Insightful/Vote Up, Awesome o/y como la respuesta aceptada. ¡Hará que sea más fácil para otros encontrar una solución o ayudar a responder!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Answers
-
Translated with Google Translate:
Hi, I would like to know if there is any way to record the time when the change to a cell is made.
Example, I have a status column: Pending-Returned-Complete, it allows me to automatically record the date the status change is made, but I need it to record the time it is done..
In order to know how many minutes it took to change from one state to another.
Is it possible with some formula?
-
English:
You would need to have a unique identifier on every row and a system generated Modified (date) type column.
Then you would set up a copy row automation to trigger whenever the status changes, and have it sent to a second sheet.
From there you would use an INDEX/MATCH to pull in the date/time stamp from the second sheet based on the unique identifier.
Google Translate:
Necesitaría tener un identificador único en cada fila y una columna de tipo Modificado (fecha) generada por el sistema.
Luego, configuraría una automatización de copia de fila para activarse cada vez que cambie el estado y enviarla a una segunda hoja.
A partir de ahí, usaría un ÍNDICE/COINCIDIR para extraer el sello de fecha/hora de la segunda hoja en función del identificador único.
-
Hi @CarolLem
I hope you're well and safe!
To add to Paul's excellent advice/answer.
Please have a look at my post below with a method I developed. (you can request a copy if you like)
More info:
Would that work/help?
I hope that helps!
Have a fantastic week & Happy New Year!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
----------------
Google Translate
Hola @CarolLem
¡Espero que estés bien y a salvo!
Para agregar al excelente consejo / respuesta de Paul.
Por favor, eche un vistazo a mi publicación a continuación con un método que desarrollé. (puede solicitar una copia si lo desea)
Más información:
¿Eso funcionaría/ayudaría?
¡Espero que eso ayude!
¡Que tengas una semana fantástica y feliz año nuevo!
Mejor,
Andrea Starå | Consultor de flujo de trabajo / CEO @ WORK BOLD
✅¿Mi(s) publicación(es) ayudó(n) o respondió su pregunta o resolvió su problema? Apoye a la comunidad marcándolo como Insightful/Vote Up, Awesome o/y como la respuesta aceptada. ¡Hará que sea más fácil para otros encontrar una solución o ayudar a responder!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
-
Yes, but I provided the link so they can request a copy of an example if they like.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Why not publish it so people don't need to request a copy?
-
Buenas otra consulta: cuanto intento realizar la formula vlookup, y quiero estirar la columna de modificacion que tiene la fecha y hora de ultima modificación, me sale error: #INVALID COLUM VALUE
=VLOOKUP([Identificador de la fila]@row; {hoja para datos de hora Calidad de Datos Rango 1}; 27; 0)
Pero si cambio por otro nro:
=VLOOKUP([Identificador de la fila]@row; {hoja para datos de hora Calidad de Datos Rango 1}; 25; 0), si me estira los datos que tiene esa columna..
El error solo me aparece cuando le selecciono que estire la columna de ultima modificación..
También probe con:
=IF(ESTADO@row = "EN PROCESO"; INDEX({hoja para datos de hora Calidad de Datos Rango 4}; MATCH([Identificador de la fila]@row; {hoja para datos de hora Calidad de Datos Rango 5})))
y el mismo error obtengo: #INVALID COLUM VALUE
-
Hi, another consult : when I try to make the vlookup formula, and I want to stretch the modification column that has the date and time of the last modification, I get an error: #INVALID COLUM VALUE =VLOOKUP([Row identifier]@row; {sheet for time data Data Quality Range 1}; 27; 0) But if I change for another number: =VLOOKUP([Row identifier]@row; {sheet for time data Data Quality Rank 1}; 25; 0), if I stretch the data that has that column.. The error only appears when I select to stretch the column of last modification.. Also try with: =IF(STATUS@row = "IN PROCESS"; INDEX({sheet for time data Data Quality Rank 4}; MATCH([Row identifier]@row; {sheet for time data Data Quality Rank 5} ))) and the same error I get: #INVALID COLUM VALUE..... ***What would be the correct way to use the index/match formula?( for me to stretch "last modified date" which contains the date and time) -
@CarolLem Try putting it the formula in a text/number column and using the below addition on the end to convert it into a text value:
=VLOOKUP([Row identifier]@row; {sheet for time data Data Quality Range 1}; 27; 0) + ""
-
Hi all,
I see this post is some time old because I need to do just what is discussed here, but by testing, I have discovered that there is already implemented an automation that allows direct to collect of the date without creating another sheet and adding a formula.
Could someone confirm that this is correct? I have checked that it is not instantaneous, but it works. My concern is that I'm missing something I'm unaware of or don't know about.
Thank you so much,
Evelio
-
@Evelio Barea This thread also captures a time stamp. If you only need a date stamp, you can use the automation. It is not instant, but it does scale with the sheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.4K Get Help
- 465 Global Discussions
- 156 Industry Talk
- 510 Announcements
- 5.4K Ideas & Feature Requests
- 86 Brandfolder
- 156 Just for fun
- 81 Community Job Board
- 518 Show & Tell
- 35 Member Spotlight
- 3 SmartStories
- 307 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I need a formula that populates the due date based on the date that its approved. I want the date to be at the end of each quarter
- I have a source sheet of different items, rolling out on different dates for different stores. I am creating a target sheet of a rollout of one specific item. I want the target sheet to list all the s…
- = "Not Started" if NO boxes checked = "In Progress" if SOME boxes are checked = "Complete" if ALL boxes are checked for the attached example I manually changed status based on checked boxes, but I'm h…