I need a formula that will calculate time worked(b-a). Automaticlly take out a lunch only if they have worked < 5hrs, rounded to the nearest 0.25.
Can somebody please help Ive looked all over the internet and tryed several formulas, but cant seem to get it to work the way I would like.
Last edited by NBVC; 01-26-2012 at 08:13 AM.
How Long would lunch be? 1 hr? so if they worked 4 hours? make it -1 so it's 3 hours?
I can round to the nearest 2 digits... but i can't specifically do .25.
Lunch would be 30 min only if they work more than 5 hrs
Does this work?
=((B1-A1+(B1<A1))*24)-(0.5*(B1<A1))
where A1 has start time, B1 has end time.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
It calculated the time but didnt take out the 30 min lunch.
How about?
=((B1-A1+(B1<A1))*24)-IF(((B1-A1+(B1<A1))*24)>5,0.5,0)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Yes that works great thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks