EXCEL For Mac

%3CLINGO-SUB%20id%3D%22lingo-sub-2605561%22%20slang%3D%22en-US%22%3EEXCEL%20For%20Mac%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2605561%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20is%20the%20correct%20formula%20for%20determining%20the%20%23%20of%20hours%20worked%20and%20not%20to%20have%20AM%2FPM%20show%20up%20in%20the%20answer%20column%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESay%20they%20worked%20from%209%3A00%20am%20to%204%3A30%20pm%20%3D%20%3F%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BObviously%20it%20is%206.5%20hours%2C%20but%20when%20I%20put%20the%20formula%20I%20think%20is%20right%2C%20it%20adds%20an%20am%2Fpm%20in%20the%20answer%20when%20all%20I%20want%20is%20the%20%23%20of%20hours.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2605561%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-2605625%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20For%20Mac%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2605625%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1119507%22%20target%3D%22_blank%22%3E%40TeresaHynes%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20the%20result%20in%20the%20format%206%3A30%2C%20apply%20the%20custom%20format%20h%3Amm%20to%20the%20cells%20with%20the%20hours%20worked%20formula.%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20the%20result%20in%20the%20format%206.5%2C%20multiply%20the%20time%20difference%20with%2024%20and%20format%20the%20cells%20with%20the%20formula%20as%20General%20or%20as%20Number%20with%20one%20or%20two%20decimal%20places.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2605748%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20For%20Mac%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2605748%22%20slang%3D%22en-US%22%3EI%20am%20so%20unfamiliar%20with%20this%2C%20but%20I%20do%20want%20it%20to%20show%20as%20%226.50%22%20in%20the%20Total%20Time%20column%20then%20subtract%20whatever%20lunch%20time%20taken%20for%20the%20Total%20Hours%20column.%20Then%20to%20add%20the%20total%20hours%20column%20%26amp%3B%20then%20multiply%20by%20wage%20per%20hour%20(120)%20plus%20expenses%20for%20a%20Grand%20Total%3C%2FLINGO-BODY%3E
Occasional Contributor

What is the correct formula for determining the # of hours worked and not to have AM/PM show up in the answer column?

 

Say they worked from 9:00 am to 4:30 pm = ?? 

 Obviously it is 6.5 hours, but when I put the formula I think is right, it adds an am/pm in the answer when all I want is the # of hours. 

8 Replies

@TeresaHynes

If you want the result in the format 6:30, apply the custom format h:mm to the cells with the hours worked formula.

If you want the result in the format 6.5, multiply the time difference with 24 and format the cells with the formula as General or as Number with one or two decimal places.

I am so unfamiliar with this, but I do want it to show as "6.50" in the Total Time column then subtract whatever lunch time taken for the Total Hours column. Then to add the total hours column & then multiply by wage per hour (120) plus expenses for a Grand Total




July

Date Time In Time Out Total Time Lunch Total Hours
7/1/21 10:00 AM 2:00 PM 0
7/2/21 10:00 AM 12:00 PM 0
7/5/21 10:00 AM 12:00 PM 0
7/6/21 9:00 AM 1:00 PM 0
7/7/21 12:30 PM 6:30 PM 0
7/8/21 8:15 AM 5:00 PM 0
7/9/21 9:00 AM 1:00 PM 0
7/12/21 8:30 AM 4:30 PM 0
7/13/21 8:30 AM 4:45 PM 0
7/14/21 8:15 AM 3:30 PM 0
7/15/21 8:00 AM 6:00 PM 0
7/16/21 9:00 AM 5:30 PM 0
7/19/21 8:00 AM 4:45 PM 0
7/20/21 8:15 AM 4:30 PM 0
7/21/21 9:00 AM 3:15 PM 0
7/22/21 8:00 AM 4:45 PM 0
7/23/21 10:00 AM 2:00 PM 0
7/26/21 9:00 AM 3:00 PM 0
7/27/21 9:00 AM 4:15 PM 0
7/28/21 10:00 AM 3:00 PM 0
7/29/21 10:00 AM 5:30 PM 0
7/30/21 9:00 AM 12:00 PM 0




120
Total
Expenses
Grand Total To Be Paid


Date Time In Time Out Total Time Lunch Total Hours
7/1/21 10:00 AM 2:00 PM 0
7/2/21 10:00 AM 12:00 PM 0
7/5/21 10:00 AM 12:00 PM 0
7/6/21 9:00 AM 1:00 PM 0
7/7/21 12:30 PM 6:30 PM 0
7/8/21 8:15 AM 5:00 PM 0
7/9/21 9:00 AM 1:00 PM 0
7/12/21 8:30 AM 4:30 PM 0
7/13/21 8:30 AM 4:45 PM 0
7/14/21 8:15 AM 3:30 PM 0
7/15/21 8:00 AM 6:00 PM 0
7/16/21 9:00 AM 5:30 PM 0
7/19/21 8:00 AM 4:45 PM 0
7/20/21 8:15 AM 4:30 PM 0
7/21/21 9:00 AM 3:15 PM 0
7/22/21 8:00 AM 4:45 PM 0
7/23/21 10:00 AM 2:00 PM 0
7/26/21 9:00 AM 3:00 PM 0
7/27/21 9:00 AM 4:15 PM 0
7/28/21 10:00 AM 3:00 PM 0
7/29/21 10:00 AM 5:30 PM 0
7/30/21 9:00 AM 12:00 PM 0




120
Total
Expenses
Grand Total To Be Paid


July
I don't know how to attach the file so you can see it exactly

@TeresaHynes 

It very depend on how your data is formatted. To attach the file on Reply to post browse for file

image.png

@TeresaHynes 

Thank you. You may use formula as

=MOD(E7-C7,1)

if you have overnight shifts, or simply = E7-C7 if not. To that cell please apply custom number format (ctrl+1 staying on cell)

image.png

if you'd like to see result as 6:30.

If as 6.5 use formula

=MOD(E7-C7,1)*24

and apply format as

image.png

Please check attached.