I have the following formula:
=TEXT(D2-C2-TIME(,60,),"[h] \h\r\s: m \m\i\n\s")
It's taking out 60 minutes for lunch. Now I want to use this to calculate straight time and not deduct any time. How can this be modified?
Thank you!
I have the following formula:
=TEXT(D2-C2-TIME(,60,),"[h] \h\r\s: m \m\i\n\s")
It's taking out 60 minutes for lunch. Now I want to use this to calculate straight time and not deduct any time. How can this be modified?
Thank you!
Hi Karen615,
Remove the
from the formula.![]()
Please Login or Register to view this content.
----
Mark threads as Solved
Star those that help
Excellent! Thank you!
Have a great weekend!![]()
Sorry, one last question...
What formula do I use to sum all these times in the following format: (10 hrs: 45 mins) if the range is cell: E2:E25?
Thank you for your help
Apologies for the delayed reply.
Use this for the sum
Where I have the F7:F14, use your Column.![]()
Please Login or Register to view this content.
Hope this helps.
Steve
Thank you so much for your help Steve, but it's not calculating. It returns "0 hrs. 0 mins"
Not sure what is wrong
Karen
Hi Karen,
Can you post a sample sheet so I can see how you are entering the data and the format for columns C and D?
I can give you better direction then. It can contain dummy data.
Thank you.
I hope I am able to attach the spreadsheet. Here goes!
Thank you for your help!
Hi Karen,
Downloaded the spreadsheet. Will post a reply soon.
Keep it simple..
Just use plain old subtraction and sum for the values.
And apply a format ([h] \h\r\s: m \m\i\n\s) to the cells with the formulas
D2 and filled down : =C2-B2
D21: =SUM(D2:D20)
Then apply the format [h] \h\r\s: m \m\i\n\s to the whole range D2:D21
Hi Jonmo1,
That is the same direction I was heading. Just downloaded her spreadsheet to show her the formatting.
@Karen,
I attached your sample spreadsheet with the changes. Column E is setup to do the math of out time minus the in time to get the total hours:minutes.
Cell E21 is setup with a format of [h]:mm so it shows total hours and minutes and does not "roll over" once it hits 24 hours (basically that is a day).
Selecting the Cells E2:E21, you can apply a Custom format of "[h] \h\r\s: m \m\i\n\s" (without the double quotes) and it will setup the format as you see on the attachment.
Hope this Helps.
Thanks for the input Jonmo1
Steve
Time Card - Test.xls
ImStevenB & Jonmo1,
Thank you so much for all your help. It works great!
Karen
You're welcome.
You're welcome Karen.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks