SOLVED

Time Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1844079%22%20slang%3D%22en-US%22%3ETime%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1844079%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20Cell%20A1%20contains%208%3A00%20AM%20and%20Cell%20B1%20contains%203%3A00%20PM%20and%20I%20wanted%20to%20know%20the%20difference%20between%20those%20two%20times%20inorder%20to%20calculate%20the%20total%20hours%20worked.%20As%20Im%20well%20aware%20of%20the%20fact%20that%20Excel%20consider%20Time%208%3A00%20AM%20as%208%2F24%20and%20for%203%3A00PM%20as%20(12%2B3)%2F24%20so%20to%20get%20my%20answer%20as%20'7%20'%20I%20used%20the%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E(B1-A1)*24%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CEM%3Edecides%20to%20mutiply%20by%2024%20get%20to%20get%20the%20number%207%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20everytime%20when%20I%20used%20the%20above%20formula%20my%20answer%20always%20comes%20as%2012%3A00%20AM%20instead%20of%207%3C%2FP%3E%3CP%3Eso%20I%20Click%20on%20the%20cell%20that%20I%20entered%20my%20formula%20and%20click%20General%20in%20Number%20formatting%20and%20when%20I%20do%20that%20I%20get%20the%20desired%20asnwer(7).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20wanted%20to%20know%20if%20there's%20a%20way%20to%20get%20the%20answer%20as%207%20without%20clicking%20eveytime%20on%20the%20cell%20to%20change%20it%20to%20general%3F%20As%20I%20dont%20understand%20Why%20I%20have%20to%20change%20to%20general%20each%20time%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1844079%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1844141%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1844141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F840530%22%20target%3D%22_blank%22%3E%40kafka42%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20%3DB1-A1%20subtracts%20two%20times%20and%20returns%20the%20result%20as%20time%2C%20which%20is%20reasonable.%3C%2FP%3E%0A%3CP%3ESometimes%2C%20you%20want%20to%20multiply%20this%2C%20for%20example%20to%20take%20overtime%20into%20account.%20For%20example%2C%20if%20time%20on%20Sunday%20counts%20double%3A%20%3D(B1-A1)*2.%20It%20is%20still%20reasonable%20to%20display%20this%20as%20time.%3C%2FP%3E%0A%3CP%3ESo%20it's%20difficult%20for%20Excel%20to%20%22know%22%20that%20you%20DON'T%20want%20%3D(B1-A1)*24%20to%20display%20as%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

If Cell A1 contains 8:00 AM and Cell B1 contains 3:00 PM and I wanted to know the difference between those two times inorder to calculate the total hours worked. As Im well aware of the fact that Excel consider Time 8:00 AM as 8/24 and for 3:00PM as (12+3)/24 so to get my answer as '7 ' I used the formula

 

(B1-A1)*24 

decides to mutiply by 24 get to get the number 7

 

But everytime when I used the above formula my answer always comes as 12:00 AM instead of 7

so I Click on the cell that I entered my formula and click General in Number formatting and when I do that I get the desired asnwer(7).

 

Just wanted to know if there's a way to get the answer as 7 without clicking eveytime on the cell to change it to general? As I dont understand Why I have to change to general each time? 

2 Replies
best response confirmed by kafka42 (New Contributor)
Solution

@kafka42 

The formula =B1-A1 subtracts two times and returns the result as time, which is reasonable.

Sometimes, you want to multiply this, for example to take overtime into account. For example, if time on Sunday counts double: =(B1-A1)*2. It is still reasonable to display this as time.

So it's difficult for Excel to "know" that you DON'T want =(B1-A1)*24 to display as time.

Thanks for your clarification!! Really helpful.