Home

Convert from standard notation to time does not work

%3CLINGO-SUB%20id%3D%22lingo-sub-765199%22%20slang%3D%22en-US%22%3EConvert%20from%20standard%20notation%20to%20time%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765199%22%20slang%3D%22en-US%22%3E%3CP%3EJI%20have%20loaded%20date%20from%20a%20database%20and%20want%20to%20convert%20that%20data%20to%20time%20but%20it%20does%20not%20work.%20The%20format%20is%20now%20standard%20en%20looks%20like%20this.%20164833.%20If%20i%20convert%20it%20to%20time%20then%20it%20becomes%2000%3A00%3A00.%20Does%20anyone%20know%20what%20to%20do.%20In%20this%20format%20i%20can%20not%20use%20it%20for%20proces%20mining.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-765199%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765253%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20from%20standard%20notation%20to%20time%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379183%22%20target%3D%22_blank%22%3E%40Gijsl%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20notation%20164833%20means%20hhmmss%2C%20then%20you%20can%20use%20this%20formula%20to%20convert%20it%20to%20proper%20time%20format%3A%3C%2FP%3E%3CPRE%3E%3DTIME(LEFT(A1%2C2)%2CMID(A1%2C3%2C2)%2CRIGHT(A1%2C2))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20855px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123874iC3F7C5C9804B9F3A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Time%20Format.png%22%20title%3D%22Time%20Format.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765254%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20from%20standard%20notation%20to%20time%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765254%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379183%22%20target%3D%22_blank%22%3E%40Gijsl%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20looking%20to%20convert%26nbsp%3B%3CSPAN%3E164833%20into%20the%20format%26nbsp%3B16%3A48%3A33%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESincerely%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EPReagan%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765256%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20from%20standard%20notation%20to%20time%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379183%22%20target%3D%22_blank%22%3E%40Gijsl%3C%2FA%3E%26nbsp%3B%20so%20if%20you%20have%20a%20serial%20date%20(just%20a%20number)%2C%20then%20use%20this%20formula%20in%20a%20cell%20(like%20D8)%20'%3DTEXT(C8%2C%22m%2Fd%2Fyyyy%22)'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edoes%20that%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765264%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20from%20standard%20notation%20to%20time%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765264%22%20slang%3D%22en-US%22%3Ewhoops%2C%20missed%20the%20boat%20on%20that%20one%2C%20ignore%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766480%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20from%20standard%20notation%20to%20time%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766480%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20that%20is%20right.%20That%20is%20what%20i%20am%20trying%20to%20achive.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766490%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20from%20standard%20notation%20to%20time%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766490%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehet%20werkt%F0%9F%91%8D%F0%9F%8F%BB%20%26nbsp%3BIt%20works.%20Tnx.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766493%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20from%20standard%20notation%20to%20time%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20almost%20perfect%20but%20de%20conversion%20is%20wrong%20with%20time%20before%20noon.%20For%20example%2093031%20is%20converted%20to%2021%3A03%3A31.%20But%20it%20is%20in%20de%20morning%20en%20not%20in%20the%20evening.%20And%2084340%20is%20converted%20in%2012%3A34%3A40.%20Do%20you%20know%20what%20i%20am%20doing%20wrong.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766508%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20from%20standard%20notation%20to%20time%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379183%22%20target%3D%22_blank%22%3E%40Gijsl%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%2C%20it%20seems%20that%20the%20formula%20has%20a%20bug!%3C%2FP%3E%3CP%3EI've%20fixed%20it%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DIF(LEN(A1)%3D5%2CTIME(LEFT(A1%2C1)%2CMID(A1%2C2%2C2)%2CRIGHT(A1%2C2))%2C%3CBR%20%2F%3ETIME(LEFT(A1%2C2)%2CMID(A1%2C3%2C2)%2CRIGHT(A1%2C2)))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766633%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20from%20standard%20notation%20to%20time%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766633%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379183%22%20target%3D%22_blank%22%3E%40Gijsl%3C%2FA%3E%3C%2FP%3E%3CP%3ETreating%20the%20original%20as%20a%20number%2C%20convert%20first%20to%20correctly%20formatted%20text%2C%20then%20back%20to%20a%20time%20value%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20TIMEVALUE(%20TEXT(number%2C%20%2200%5C%3A00%5C%3A00%22)%20)%3C%2FFONT%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Gijsl
Occasional Contributor

JI have loaded date from a database and want to convert that data to time but it does not work. The format is now standard en looks like this. 164833. If i convert it to time then it becomes 00:00:00. Does anyone know what to do. In this format i can not use it for proces mining. 

10 Replies

@Gijsl

 

Hi,

 

If this notation 164833 means hhmmss, then you can use this formula to convert it to proper time format:

=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

Time Format.png

 

Hope that helps 

Hello @Gijsl,

 

Are you looking to convert 164833 into the format 16:48:33?

 

Sincerely,

PReagan

@Gijsl  so if you have a serial date (just a number), then use this formula in a cell (like D8) '=TEXT(C8,"m/d/yyyy")'

 

does that help?

whoops, missed the boat on that one, ignore this:

@PReagan 

 

Yes that is right. That is what i am trying to achive.  

@Haytham Amairah 

 

het werkt👍🏻  It works. Tnx. 

@Haytham Amairah 

 

It is almost perfect but de conversion is wrong with time before noon. For example 93031 is converted to 21:03:31. But it is in de morning en not in the evening. And 84340 is converted in 12:34:40. Do you know what i am doing wrong. 

@Gijsl

 

Sorry, it seems that the formula has a bug!

I've fixed it as follows:

=IF(LEN(A1)=5,TIME(LEFT(A1,1),MID(A1,2,2),RIGHT(A1,2)),
TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)))

 

@Gijsl

Treating the original as a number, convert first to correctly formatted text, then back to a time value

= TIMEVALUE( TEXT(number, "00\:00\:00") ) 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies