Hi
Please find attached file for your ref. Can someone please
1) provide a formula that can minus out 12pm to 1pm lunch hour in column F?
2) provide a good website that explain times function in excel?
Thanks
Hi
Please find attached file for your ref. Can someone please
1) provide a formula that can minus out 12pm to 1pm lunch hour in column F?
2) provide a good website that explain times function in excel?
Thanks
Hi,
you could subtract -1/24 (one hour)
=C1-B1-1/24
Hope it helps
http://www.cpearson.com/excel/datearith.htm
Last edited by canapone; 01-27-2012 at 11:02 PM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
CANAPONE's solution works if the shift always includes the time from 12-1. Harrold, that's what your examples show but is that always the case? Your formulas seem to show that you have a good grasp on time calculations. Here is a page on dates and times from a very good Excel site.
Hi Canapone,
Thanks for your reply.
i am doing payroll and there lots of O/T claims. If possible, i would like to find a formula that can automate deduct 12pm-1pm from the calculation. i prefer not to manually amend some of the formula where those time are involved. It resulted formula not consisitent.
Thanks
Hi 6StringJazzer,
No, it is not.Harrold, that's what your examples show but is that always the case?
Basically, everything is done manually. the workers fill in the OT form stating OT start & finish. Some of them cheated by include normal working hours, same OT to claim more than once, etc. The supervisors approve these OT without checking properly. So now, i need to use Excel to calculate the actual OT hours. and with a formula to calculate without lunch hour will make my work easier.
Thanks
Hi,
example: the formula does not consider hours or minutes from 12 am to 1 pm
RegardsPlease Login or Register to view this content.
Last edited by canapone; 01-27-2012 at 11:46 PM.
Hi Canapone,
I tried your formula and dont know whether i have put in the correct cell. Is it possible to upload a file for me?
Really appreciate for your advice.
Thanks
Hi,
after double-checking my previous attempt, I prefer
The formula cuts off period 12:00 am 1:00 pmPlease Login or Register to view this content.
See the example attached
Let me know if you get unwanted results.
You'll get for sure better solutions.
Regards
Last edited by canapone; 01-29-2012 at 02:51 AM. Reason: no ";" in American standard formula
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks