SOLVED

Excel Time Function

%3CLINGO-SUB%20id%3D%22lingo-sub-2399918%22%20slang%3D%22en-US%22%3EExcel%20Time%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399918%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20this%20time%20function%20column%20if%20we%20drag%20the%20cursor%20on%20the%20time%20format%20it%20is%20not%20showing%20the%20SUM%20in%20Taskbar%20sir.%20Only%20the%20count%20of%20the%20no%20of%20rows%20in%20the%20column%20is%20shown.%20I%20want%20to%20have%20the%20sum%20of%20the%20time%20in%20the%20above%20time%20column%20sir.%3C%2FP%3E%3CP%3EColumn%20J%20and%20K%20time%20function%20is%20in%20text%20format%20sir.%20And%20using%20the%20Time%20format%20i%20have%20converted%20it%20into%20time%20format%20sir%20but%20for%20time%20less%20than%2024%20hours%20the%20solution%20is%20right%20but%20for%20more%20than%2024%20hours%20it%20is%20not%20giving%20the%20correct%20answer%20using%20Custom%20format%20%5BH%5D%3AMM%3ASS%20also%20sir.%3C%2FP%3E%3CP%3EAfter%2024%20hours%20of%20time%20it%20is%20giving%20the%20Remainder%20answer%20to%20the%20solution%20sir.%20If%20it%20is%2026%3A00%20hours%20the%20solution%20is%20giving%20as%202%3A00%20hours.%20Please%20solve%20it%20sir.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2399918%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2399990%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Time%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1067188%22%20target%3D%22_blank%22%3E%40kiranjab%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUM(MID(J3%2C%7B1%3B5%3B8%7D%2C%7B3%3B2%3B2%7D)%2F%7B24%3B1440%3B86400%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2399998%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Time%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399998%22%20slang%3D%22en-US%22%3E%3DTEXT(RIGHT(J5%2C8)%2C%22%5Bhh%5D%3Amm%3Ass%22)%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2400021%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Time%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2400021%22%20slang%3D%22en-US%22%3EThank%20you%20sir.%20It%20worked%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2402412%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Time%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2402412%22%20slang%3D%22en-US%22%3Esir%20it%20is%20working%20for%20less%20than%20100%20hours%20only%20sir%20i.e%2C%2099%3A59%3A59%3C%2FLINGO-BODY%3E
Occasional Contributor

In this time function column if we drag the cursor on the time format it is not showing the SUM in Taskbar sir. Only the count of the no of rows in the column is shown. I want to have the sum of the time in the above time column sir.

Column J and K time function is in text format sir. And using the Time format i have converted it into time format sir but for time less than 24 hours the solution is right but for more than 24 hours it is not giving the correct answer using Custom format [H]:MM:SS also sir.

After 24 hours of time it is giving the Remainder answer to the solution sir. If it is 26:00 hours the solution is giving as 2:00 hours. Please solve it sir.

5 Replies

@kiranjab 

=SUM(MID(J3,{1;5;8},{3;2;2})/{24;1440;86400})
best response confirmed by allyreckerman (Microsoft)
Solution
=TEXT(RIGHT(J5,8),"[hh]:mm:ss")


Thank you sir. It worked
sir it is working for less than 100 hours only sir i.e, 99:59:59

@kiranjab 

I guess @Riny_van_Eekelen already answered in another thread. Something like

=TIMEVALUE(IF(LEFT(J3)="0",REPLACE(J3,1,1,),J3))