Home

Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-721945%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-721945%22%20slang%3D%22en-US%22%3E%3CP%3EA1%3D14%2CThen%20Result%20B1%3D01%3A10%20if%20A1%20is%20multiply%20by%205%20Minute%20then%20B1%20is%20in%20HH%3AMM%20Formet%2C%20what%20is%20formula%20for%20B1%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-721945%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-722632%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722632%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F366974%22%20target%3D%22_blank%22%3E%40Hardik_Gadhavana%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20most%20certainly%20not%20the%20most%20efficient%20formula%20but%20it%20will%20do%20the%20trick.%20If%20A1%3D14%20and%20B1%3DA1*5%2C%20then%20C1%20has%20the%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ROUNDDOWN(B1%2F60%2C0)%26lt%3B10%2C%220%22%26amp%3BROUNDDOWN(B1%2F60%2C0)%2CROUNDDOWN(B1%2F60%2C0))%26amp%3B%22%3A%22%26amp%3BIF((B1-ROUNDDOWN(B1%2F60%2C0)*60)%26lt%3B10%2C%220%22%26amp%3B(B1-ROUNDDOWN(B1%2F60%2C0)*60)%2C(B1-ROUNDDOWN(B1%2F60%2C0)*60))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3EPReagan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-722698%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F366974%22%20target%3D%22_blank%22%3E%40Hardik_Gadhavana%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DTIME(1%2C10%2C0)%2B(A1-14)*5%2F24%2F60%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-724670%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-724670%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F366974%22%20target%3D%22_blank%22%3E%40Hardik_Gadhavana%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20appears%20that%20you%20want%20to%20multiply%20A1%20by%205%20minutes%20and%20return%20the%20result%20formatted%20as%20hh%3Amm.%20Thus%2C%20you%20need%20this%20formula%20in%20B1%2C%20copied%20down%20rows%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DA1*TIME(0%2C5%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Hardik_Gadhavana
Occasional Visitor

A1=14,Then Result B1=01:10 if A1 is multiply by 5 Minute then B1 is in HH:MM Formet, what is formula for B1?

 

3 Replies

Hello @Hardik_Gadhavana,

 

This is most certainly not the most efficient formula but it will do the trick. If A1=14 and B1=A1*5, then C1 has the formula:

 

=IF(ROUNDDOWN(B1/60,0)<10,"0"&ROUNDDOWN(B1/60,0),ROUNDDOWN(B1/60,0))&":"&IF((B1-ROUNDDOWN(B1/60,0)*60)<10,"0"&(B1-ROUNDDOWN(B1/60,0)*60),(B1-ROUNDDOWN(B1/60,0)*60))

 

Hope this helps!

PReagan

 

@Hardik_Gadhavana 

That could be

=TIME(1,10,0)+(A1-14)*5/24/60

@Hardik_Gadhavana 

It appears that you want to multiply A1 by 5 minutes and return the result formatted as hh:mm. Thus, you need this formula in B1, copied down rows: 

=A1*TIME(0,5,0)

Related Conversations