Excel Datetime

%3CLINGO-SUB%20id%3D%22lingo-sub-3016995%22%20slang%3D%22en-US%22%3EExcel%20Datetime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3016995%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20time%20is%20displaying%20as%20219%3A38%3A50%20but%20when%20I%20click%20on%20it%20it%20displays%20as%26nbsp%3B1900%2F01%2F09%2003%3A38%3A50%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehow%20do%20I%20remove%20the%20date%20and%20still%20keep%20it%20as%20219%3A38%3A50%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3016995%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3017042%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Datetime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3017042%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1233231%22%20target%3D%22_blank%22%3E%40Bernadine945%3C%2FA%3E%26nbsp%3B219%3A38%3A50%20is%20recognised%20as%20the%20219th%20hour%20(plus%20minites%20and%20seconds)%20starting%20from%20January%201%2C%201900%2C%20the%20first%20day%20in%20Excels's%20date%20system.%20Excel%20figured%20out%20that%20you%20want%20to%20display%20it%20as%20hours%3Aminutes%3Aseconds%20and%20has%20applied%20a%20custom%20format%20to%20it%20%3CSTRONG%3E%5Bh%5D%3Amm%3Ass%3C%2FSTRONG%3E.%20However%2C%20the%20underlying%20value%20is%20still%20number%20representing%20date%20and%20time.%20That's%20why%20you%20see%20the%201900%20date%20with%20time%20in%20the%20formula%20bar.%20If%20you%20do%20not%20need%20to%20calculate%20with%20the%20hours%20value%20entered%2C%20format%20the%20cell%2Fcolumn%20where%20you%20intend%20to%20enter%20these%20values%20%3CSTRONG%3Eas%20text%3C%2FSTRONG%3E.%20Then%2C%20they%20will%20remain%20text%20(as%20entered)%2C%20i.e.%20not%20change%20to%20a%20date%2Ftime%20format.%20Alternatively%2C%20enter%20an%20apostrophe%20or%20a%20space%20before%20the%20hhh%3Amm%3Ass%20when%20you%20enter%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi 

 

the time is displaying as 219:38:50 but when I click on it it displays as 1900/01/09 03:38:50

 

how do I remove the date and still keep it as 219:38:50

2 Replies

@Bernadine945 219:38:50 is recognised as the 219th hour (plus minites and seconds) starting from January 1, 1900, the first day in Excels's date system. Excel figured out that you want to display it as hours:minutes:seconds and has applied a custom format to it [h]:mm:ss. However, the underlying value is still number representing date and time. That's why you see the 1900 date with time in the formula bar. If you do not need to calculate with the hours value entered, format the cell/column where you intend to enter these values as text. Then, they will remain text (as entered), i.e. not change to a date/time format. Alternatively, enter an apostrophe or a space before the hhh:mm:ss when you enter it.

hi @Riny_van_Eekelen 

 

thank you so much the apostrophe worked 

 

have a nice day