Time formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1669845%22%20slang%3D%22en-US%22%3ETime%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1669845%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help.%20I%20am%20trying%20to%20enter%20times%20into%20a%20worksheet%20with%20raw%20data%3A%201630%20%2C%20(no%20punctuation%2Fcolons)%20to%20give%20me%20result%2016%3A30.%20I%20have%20tried%20formatting%20the%20cell%20with%2000%2F%3A00%20which%20works%20but%20it%20needs%20to%20be%2024hr%20format%20so%20I%20can%20subtract%20one%20cell%20from%20another%20and%20still%20get%20an%20answer%20in%2024hr%20format.%20If%20I%20try%20hh%2F%3Amm%20the%20formula%20bar%20comes%20up%20with%2017%2F06%2F1904%2000%3A00%3A00.%20I%20don't%20know%20why%20it's%20giving%20me%20a%20date.%20I%20don't%20need%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1669845%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-1670187%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1670187%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F794530%22%20target%3D%22_blank%22%3E%40adamodonohue%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EHave%20you%20tried%20hhmm%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bennadeau_0-1600190860428.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218417i8C6FC4F9061FDEB4%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Bennadeau_0-1600190860428.png%22%20alt%3D%22Bennadeau_0-1600190860428.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi there,

 

I need help. I am trying to enter times into a worksheet with raw data: 1630 , (no punctuation/colons) to give me result 16:30. I have tried formatting the cell with 00/:00 which works but it needs to be 24hr format so I can subtract one cell from another and still get an answer in 24hr format. If I try hh/:mm the formula bar comes up with 17/06/1904 00:00:00. I don't know why it's giving me a date. I don't need it.

 

Thanks in advance

2 Replies

Hi @adamodonohue,

Have you tried hhmm

Bennadeau_0-1600190860428.png

 

 

@adamodonohue 

In Excel dates are integers where starting from Jan 01, 1900 (which is equal to 1). Time is decimal part of the number (1 hour = 1/24).

 

Thus value 1630 in terms of date will be 1630th day from 01/01/1900, or June 17, 1904. Since no decimal part was added it shows 00:00 time.

 

You can't convert  dates (integers)  to time (decimals) just by formatting, only by formulas.