Home

Timestamp formula causes error

%3CLINGO-SUB%20id%3D%22lingo-sub-557579%22%20slang%3D%22en-US%22%3ETimestamp%20formula%20causes%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-557579%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20at%20the%20moment%20working%20on%20creating%20a%20form%20that%20will%20be%20used%20at%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20doing%20so%20I'd%20like%20the%20form%20to%20automatically%20timestamp%20itself%20upon%20creation.%20It%20appeared%20however%2C%20that%20that%20was%20not%20easily%20doable%20and%20so%20I%20set%20out%20to%20find%20an%20alternative%20solution.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20stumbled%20upon%20this%20formula%3A%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%3E%3DIF(A2%26lt%3B%26gt%3B%22%22%2CIF(B2%26lt%3B%26gt%3B%22%22%2CB2%2CNOW())%2C%22%22)%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BThe%20function%20is%20supposed%20to%20be%20inserted%20into%20B2%20and%20should%20only%20return%20a%20timestamp%20when%20something%20is%20entered%20in%20A2%20-%20but%20every%20time%20I%20insert%20the%20function%20in%20B2%20I%20get%20an%20error%20asking%20me%20if%20I%20am%20really%20trying%20to%20insert%20text%20rather%20than%20a%20function..%3F%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20ended%20up%20solving%20the%20problem%20by%20changing%20the%20function%20to%3A%26nbsp%3B%3CBR%20%2F%3E%3DIF(A2%26lt%3B%26gt%3B%22%22%3BTODAY()%3B%22%22)%26nbsp%3B%3C%2FP%3E%3CP%3E...Same%20result...%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20-%20I%20would%20love%20to%20know%20if%20you%20can%20figure%20out%20what%20caused%20the%20first%20function%20to%20malfunction%3F%3CBR%20%2F%3E%3CBR%20%2F%3EBr.%3C%2FP%3E%3CP%3ERobusfar%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-557579%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-560786%22%20slang%3D%22en-US%22%3ERe%3A%20Timestamp%20formula%20causes%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560786%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339905%22%20target%3D%22_blank%22%3E%40Robusfar%3C%2FA%3E%26nbsp%3B%2C%20your%20first%20formula%20for%20timestamp%20works%20if%20only%20you%20enable%20iterative%20calculations%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20822px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113039i8AD88B448F44F75B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EBe%20careful%20with%20this%20option%20to%20avoid%20possible%20side%20effects.%3C%2FP%3E%0A%3CP%3EBit%20more%20details%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.spreadsheetweb.com%2Fexcel-iterative-calculation%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.spreadsheetweb.com%2Fexcel-iterative-calculation%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Robusfar
New Contributor

Dear all

I am at the moment working on creating a form that will be used at work.

 

In doing so I'd like the form to automatically timestamp itself upon creation. It appeared however, that that was not easily doable and so I set out to find an alternative solution.


I stumbled upon this formula: 

=IF(A2<>"",IF(B2<>"",B2,NOW()),"")

 The function is supposed to be inserted into B2 and should only return a timestamp when something is entered in A2 - but every time I insert the function in B2 I get an error asking me if I am really trying to insert text rather than a function..? 

I ended up solving the problem by changing the function to: 
=IF(A2<>"";TODAY();"") 

...Same result...

But - I would love to know if you can figure out what caused the first function to malfunction?

Br.

Robusfar

1 Reply
Highlighted

@Robusfar , your first formula for timestamp works if only you enable iterative calculations

image.png

Be careful with this option to avoid possible side effects.

Bit more details is here https://www.spreadsheetweb.com/excel-iterative-calculation/

Related Conversations