Home

Converting time into a corresponding number

%3CLINGO-SUB%20id%3D%22lingo-sub-400946%22%20slang%3D%22en-US%22%3EConverting%20time%20into%20a%20corresponding%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-400946%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20hoping%20someone%20could%20help%20me%20with%20this%20question.%20I%20need%20to%20convert%20a%20time%20sting%20(not%20sure%20if%20that's%20the%20right%20terminology)%20into%20a%20corresponding%20number.%20So%20in%20cell%20A1%20(time)%20the%20input%20would%20be%206%3A00am-8%3A00am%20and%20the%20corresponding%20number%20would%20be%20in%20cell%20B1%20(units)%208.%20Every%2015%20minutes%20is%20equal%20to%201%20unit.%20If%20anyone%20can%20give%20me%20any%20answers%20to%20this%20question%20it%20would%20be%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-400946%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401050%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20time%20into%20a%20corresponding%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401050%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%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%3BI%20really%20appreciate%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-400962%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20time%20into%20a%20corresponding%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-400962%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20using%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3D(MID(A2%2CSEARCH(%22-%22%2CA2)%2B1%2CLEN(A2)-SEARCH(%22-%22%2CA2)-2)-LEFT(A2%2CSEARCH(%22-%22%2CA2)-3))*24*4%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F105957iEEE3C87C80D871DE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Convert%20time%20to%20a%20special%20units.png%22%20title%3D%22Convert%20time%20to%20a%20special%20units.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F315003%22%20target%3D%22_blank%22%3E%40Chobbs323%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Chobbs323
New Contributor

I was hoping someone could help me with this question. I need to convert a time sting (not sure if that's the right terminology) into a corresponding number. So in cell A1 (time) the input would be 6:00am-8:00am and the corresponding number would be in cell B1 (units) 8. Every 15 minutes is equal to 1 unit. If anyone can give me any answers to this question it would be greatly appreciated. 

2 Replies
Highlighted

Hi,

 

I suggest using this formula:

=(MID(A2,SEARCH("-",A2)+1,LEN(A2)-SEARCH("-",A2)-2)-LEFT(A2,SEARCH("-",A2)-3))*24*4

Convert time to a special units.png

 

Hope that helps

 

@Chobbs323 

Thanks @Haytham Amairah I really appreciate it.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies