Home

Excel Formula - Date and Time

%3CLINGO-SUB%20id%3D%22lingo-sub-664068%22%20slang%3D%22en-US%22%3EExcel%20Formula%20-%20Date%20and%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-664068%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20407px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116949iA18B65C83E8F3E20%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel%20Snippet.JPG%22%20title%3D%22Excel%20Snippet.JPG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECreated%20Date%20is%20formatted%20as%20'General'.%20Last%20Modified%20Date%20is%20formatted%20as%20'Custom'%20(dd%2Fmm%2Fyyyy%20hh%3Amm)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPlease%20could%20someone%20show%20me%20a%20way%20to%20find%20the%20difference%20between%20these%20two%20cells.%3C%2FP%3E%3CP%3EI%20would%20then%20like%20to%20plot%20the%20output%20into%20a%20graph%20(with%20the%20rest%20of%20the%20data%20set).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-664068%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-664859%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20Date%20and%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-664859%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F353543%22%20target%3D%22_blank%22%3E%40sam_joyce%3C%2FA%3E%26nbsp%3Byou%20are%20not%20going%20to%20be%20able%20to%20compare%20both%20fields%20as%20they%20are%2C%20you%20need%20to%20transform%20first%20one%20into%20a%20valid%20date%2Ftime%20format%2C%20maybe%20you%20can%20use%20string%20formulas%20to%20extract%20valid%20information%20into%20a%20new%20cell%20and%20then%20convert%20it%20into%20a%20date%2Ftime%20format%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-665011%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20Date%20and%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-665011%22%20slang%3D%22en-US%22%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342487%22%20target%3D%22_blank%22%3E%40CarlosCorral%3C%2FA%3E%20-%20appreciate%20your%20help!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-665013%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20Date%20and%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-665013%22%20slang%3D%22en-US%22%3E%3CP%3EI%20can't%20see%20how%20to%20start%20a%20conversation%20in%20the%20website.%26nbsp%3B%20Can%20you%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20build%20extra%20spreadsheets%20off%20the%20master%20spreadsheet.%26nbsp%3B%20So%20when%20I%20change%20a%20name%20on%20the%20master%2C%20it%20changes%20all%20the%20other%20spreadsheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F353543%22%20target%3D%22_blank%22%3E%40sam_joyce%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-665810%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20Date%20and%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-665810%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F353543%22%20target%3D%22_blank%22%3E%40sam_joyce%3C%2FA%3E%26nbsp%3B%2C%20if%20first%20time%20is%20always%20with%20Z%20(no%20time%20zone%20offset)%2C%20conversion%20could%20be%20as%3C%2FP%3E%0A%3CPRE%3E%3DSUBSTITUTE(LEFT(A1%2CLEN(A1)-1)%2C%22T%22%2C%22%20%22)%2B0%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-665811%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20Date%20and%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-665811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F353707%22%20target%3D%22_blank%22%3E%40robin01%3C%2FA%3E%26nbsp%3B%2C%20to%20start%20new%20conversation%20is%20from%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
sam_joyce
New Contributor

Excel Snippet.JPGCreated Date is formatted as 'General'. Last Modified Date is formatted as 'Custom' (dd/mm/yyyy hh:mm)

Please could someone show me a way to find the difference between these two cells.

I would then like to plot the output into a graph (with the rest of the data set).

5 Replies

@sam_joyce you are not going to be able to compare both fields as they are, you need to transform first one into a valid date/time format, maybe you can use string formulas to extract valid information into a new cell and then convert it into a date/time format

Thanks @CarlosCorral - appreciate your help!

I can't see how to start a conversation in the website.  Can you help?

 

I am trying to build extra spreadsheets off the master spreadsheet.  So when I change a name on the master, it changes all the other spreadsheets.

 

@sam_joyce 

@sam_joyce , if first time is always with Z (no time zone offset), conversion could be as

=SUBSTITUTE(LEFT(A1,LEN(A1)-1),"T"," ")+0

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
17 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies