SOLVED

Creating a static time stamp in Excel online version

%3CLINGO-SUB%20id%3D%22lingo-sub-1787516%22%20slang%3D%22en-US%22%3ECreating%20a%20static%20time%20stamp%20in%20Excel%20online%20version%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1787516%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20found%20this%20formula%2C%20wich%20should%20work%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(B1%26lt%3B%26gt%3B%22%22%3BIF(A1%26lt%3B%26gt%3B%22%22%3BA1%3BNOW())%3B%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20as%20output%20it%20writes%2000.01.1900%20(I%20get%20the%20same%20result%20in%20Excel%20desktop%20version)%3C%2FP%3E%3CP%3EIs%20this%20a%20bug%20in%20the%20latest%20EXCEL%20version%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I'm%20using%20the%20NOW()%20function%20alone%2C%20I%20get%20the%20timestamp%20of%20presumably%20Redmont%2C%20but%20I%20am%20based%20in%20Europe.%20Where%20can%20I%20set%20the%20timezone%20in%20my%20online%20version%20of%20excel%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EM.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1787516%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1787645%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20static%20time%20stamp%20in%20Excel%20online%20version%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1787645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834859%22%20target%3D%22_blank%22%3E%40useruser%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20this%20link%20recently%20I've%20solved%2C%20will%20help%20you%20to%20get%20Static%20Time%2C%20works%20with%20all%20versions%20of%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsuperuser.com%2Fquestions%2F1592650%2Fhow-to-run-multimple-timestamps-in-an-excel-sheet%2F1592665%231592665%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGet%20static%20Time%20in%20Excel%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E**%3C%2FSTRONG%3E%26nbsp%3B%3CFONT%20size%3D%222%22%20color%3D%22%230000FF%22%3E%3CEM%3EIf%20you%20find%20this%20works%20for%20you%20then%20please%20mark%20my%20post%20as%20Best%20Solution%20as%20well%20like.%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1787672%22%20slang%3D%22de-DE%22%3ESubject%3A%20Creating%20a%20static%20time%20stamp%20in%20Excel%20online%20version%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1787672%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834859%22%20target%3D%22_blank%22%3E%40useruser%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI'm%20not%20an%20Office%20Online%20user%2C%20but%20after%20my%20researchers%20I%20found%20this%20information.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EIf%20it%20helps%2C%20I'll%20be%20happy%2C%20if%20not%20just%20ignore%20it.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22user-login%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSTRONG%3E%3CSPAN%20class%3D%22%22%3EChange%20language%20in%20Office%20365%20(Microsoft%20365)%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EOpen%20the%20Microsoft%20365%20website%20and%20log%20into%20your%20account.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EClick%20on%20the%20gear%20symbol%20in%20the%20upper%20right%20so%20that%20the%20%22Settings%22%20open.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESelect%20the%20%22Language%20and%20time%20zone%22%20category%20here.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EFind%20the%20language%20you%20want%20and%20confirm%20with%20%22Save%22.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EOffice%20365%20(Microsoft%20365)%20is%20then%20displayed%20in%20the%20selected%20language.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22user-login%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22user-login%22%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1787696%22%20slang%3D%22en-US%22%3EBetreff%3A%20Creating%20a%20static%20time%20stamp%20in%20Excel%20online%20version%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1787696%22%20slang%3D%22en-US%22%3EUsing%20NOW(%20)%20will%20never%20solves%20the%20issue%20and%20it's%20not%20problem%20with%20Time%20format%2C%2C%2C%20if%20NOW(%20)%20is%20used%20in%20the%20formula%2C%2C%20then%20every%20time%20when%20Excel%20recalculates%20the%20sheet%20you%20get%20recent%20Time%2C%2C%20since%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834859%22%20target%3D%22_blank%22%3E%40useruser%3C%2FA%3E%20wants%20STATIC%20Time%20!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1787861%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20static%20time%20stamp%20in%20Excel%20online%20version%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1787861%22%20slang%3D%22en-US%22%3Ehi%20rajesh%2C%3CBR%20%2F%3Ethank%20you%20for%20your%20reply!%3CBR%20%2F%3Eyour%20formula%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(G8%26lt%3B%26gt%3B%22%22%2CIF(E8%3D%22%22%2CNOW()%2CE8)%2C%22%22)%3CBR%20%2F%3E%3CBR%20%2F%3Edoes%20almost%20the%20same%20as%20mine%2C%20I%20tried%20this%20version%20also%2C%20but%20the%20result%20is%20still%2000.01.1900.%3CBR%20%2F%3E%3CBR%20%2F%3Ethat%20is%20my%20problem%2C%3CBR%20%2F%3Eany%20ideas%3F%3CBR%20%2F%3Ethanks%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have found this formula, wich should work:

 

=IF(B1<>"";IF(A1<>"";A1;NOW());"")

 

but as output it writes 00.01.1900 (I get the same result in Excel desktop version)

Is this a bug in the latest EXCEL version?

 

If I'm using the NOW() function alone, I get the timestamp of presumably Redmont, but I am based in Europe. Where can I set the timezone in my online version of excel?

 

Thanks

M.

9 Replies
Highlighted

@useruser 

 

Check this link recently I've solved, will help you to get Static Time, works with all versions of Excel.

 

Get static Time in Excel  

 

 

** If you find this works for you then please mark my post as Best Solution as well like.

 

Highlighted

@useruser 

I'm not an Office Online user, but after my researchers I found this information.
If it helps, I'll be happy, if not please just ignore it.

 

Highlighted
Using NOW( ) will never solves the issue and it's not problem with Time format,,, if NOW( ) is used in the formula,, then every time when Excel recalculates the sheet you get recent Time,, since @useruser wants STATIC Time !!
Highlighted
hi rajesh,
thank you for your reply!
your formula

=IF(G8<>"",IF(E8="",NOW(),E8),"")

does almost the same as mine, I tried this version also, but the result is still 00.01.1900.

that is my problem,
any ideas?
thanks

Highlighted
Thanks I will try this for the time zone settings
Highlighted
Read carefully INSTRUCTION I've written with my answer/post @Superuser (the Link) ,,, unfortunately you have skip the major part "How to get Static Date",,, or better share your WB and let me examine & fix it !
Highlighted
Best Response confirmed by useruser (Occasional Contributor)
Solution

@useruser 

Excel Online has no option to switch-on iterative calculations which are required for time stamp trick. VBA doesn't work on Excel Online and I'm not sure if Office Script could be triggered by cell value change.

 

Having circular reference Excel returns zero as result for such formulas. With applied date format for such cell it is correctly shown as Jan 00, 1900 (or 00.01.1900). No need to change regional setting for your OneDrive or SharePoint site (procedure is slightly different for them), if only for training purposes.

 

What could be done - add time stamp using iterative calculation technique in Excel Desktop. If open in Excel Online, after some confirmation, time stamp continue to work in Online version as well. I'm not sure how reliable such solution, will it work for different users, in co-authoring mode, after any file update, etc. If works that will be your risks.

Highlighted

@Sergei Baklan 

thank you for your suggestion.

Creating the file first on desktop and then uploading it worked.

And I found a workaround for the Time difference as it is 9 hours which are 0,375 of a day I add that like this:

 

=IF(G8<>"",IF(E8="",NOW()+0,375,E8),"")

 

thanks again

 

Highlighted