Hi there!
I have come across several other posts looking for a similar formula, but not quite what I am needing help with.
I am trying to find a formula that will calculate lunch and dinner penalties which occur 6 hours and 1 minute after the crew member's call time. And they continue to add up, 1 every 30 minutes, until the crew breaks for lunch.
I've attached an excel file as an example.
Thank you in advanced!
In H4 copied down (formatted as General)
In I4 copied downFormula:
Please Login or Register to view this content.
Does that do what you wanted?Formula:Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
That's a good formula, ChemistB! First time seeing the use of double-quotes in a formula like this. Here's a Rep for you.
Just wonder, can you use CEILING instead of FLOOR and dropping the "+1" at the end of the formula?
Joseph, the simple way is to try it yourself, then you can learn. i think it will work with ceiling but the "6:01" needs to be "6:00"
=CEILING(E7-D7-"6:00", "0:30")/"0:30"
Try this
In K4, lunch penalties
=CEILING((E4-D4-"6:01")/ "0:30",1)
In L4, Dinner penalties.
=CEILING((G4-F4-"6:01")/ "0:30",1)
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
That's right, thanks davsth!
Last edited by AliGW; 07-29-2021 at 03:46 AM. Reason: PLEASE don't quote unnecessarily!
Thank you everyone, out of the numerous threads with a similar request, this was the first that was what I needed.
Will save me HOURS over the long run.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks