I got a real tough problem with time formula's...anyone care to tackle it?

dfournier · Jan 2, 2009

Hello all...

I have created a employee schedule and I have received excellent help on here with a few issues (Thanks you Barry Houdini!). I now have one that is rather difficult.

Our restaurants are open from 6 am to midnight with some being open until 1 am on certain days.

Employees come in earlier than 6 am and leave later than 1am to clean etc. so this is basically close to a 24 hour schedule.

We track labor via periods, as follows

breakfast (5 am- 10:30 am)
Lunch (10:30-2 pm)
Snack (2pm- 5 pm)
Dinner (5pm-9pm)
and late night 9pm-midnight ( and in some cases on certain days until 1am)

I wanted to be able to extract hours from an employees shift to determine labor per period above (Example...Joe works from 8-2 @ 7 dollars per hour...after I put in projected sales for each period above in a seperate cell I can now tell how much it is costing me labor % wise to have joe on the clock for each period listed above.)

I have received excellent help on here and everything is fine...except the following.

When trying to extrapilate the hours for late night (9 pm to 1 am), the shift starts on one day and runs into another...EXCEL does not like negative number and therefore will not extrapilate the hours for the late night period.

For example...Tom works 5pm -1am

Excel can extract the Dinner hours scheduled (5pm-9pm) but it cannot extract the late night hours 9pm -1am because it goes into another day.

Here is the formula I am using to extract the hours from the scheduled shift for each day part...

=SUM(IF(C4:C18<="14:00"+0,IF(D4:D18>="10:30"+0,IF(D4:D18<"14:00"+0,D4:D18,"14:00")-IF(C4:C18>"10:30"+0,C4:C18,"10:30"))))*24

This above is the lunch formula. It basically states that all hours in cells c4-c18 (which is the employee IN) and the hours in cells D4- D18 (Employee OUT) that are greater than 10:30 am and less than 2:00pm will be extracted placed in the LUNCH HOURS SCHEDULED cell a the bottom of the schedule for labor calculation for that day part.

If JOE IS SCHEDULED TO OPEN AT 8AM AND CLOSE AT 2PM...the above formula will only take out 3.5 hours (10:30-2) and place it in the lunch hours scheduled box for that day.

MY PROBLEM is this...

The above formula, courtesy of Barry Houdini (of these forums) does not work for a shift that starts on one day and ends on another..it will not calculate late night hours such as 9PM to 2 AM.

How can I do this? This one thing is holding me up and I am already past the deadline...any help would be appreciated.

Could the below information be useful to anyone that might wanta look at this problem?

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CDarcy%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CDarcy%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CDarcy%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} @font-face {font-family:Consolas; panose-1:2 11 6 9 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:modern; mso-font-pitch:fixed; mso-font-signature:-1610611985 1073750091 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} p.MsoPlainText, li.MsoPlainText, div.MsoPlainText {mso-style-noshow:yes; mso-style-priority:99; mso-style-link:"Plain Text Char"; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.5pt; font-family:Consolas; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} span.PlainTextChar {mso-style-name:"Plain Text Char"; mso-style-noshow:yes; mso-style-priority:99; mso-style-unhide:no; mso-style-locked:yes; mso-style-link:"Plain Text"; mso-ansi-font-size:10.5pt; mso-bidi-font-size:10.5pt; font-family:Consolas; mso-ascii-font-family:Consolas; mso-hansi-font-family:Consolas;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> forcing excel to ignore the 24 hour format
<o

> </o

>
=Sum(A1:A5) would result in the total hours of that range if A1:A5 contained valid times. There is however a big "Gotcha!" and that is, unless told otherwise, Excel will not add past 24 hours. This is because when the time value exceeds 24 hours (true value of 1, or 1 day) it rolls into a new day and Start s again. To force Excel not to default back to a new day after 24 hours we use a cell Format of 37:30:55 or a Custom format of [h]:mm:ss

Thank you in advance for any and all consideration.

dfournier · Jan 3, 2009

Thank you Jenny...I was aware of that formula but was not sure how to put it into the code to make it work...it appears, so far, that the answer was easier than it appeared.

see my post above.

I am hoping this has been resolved, if it has, thank you to all that offered suggestions. If it does not work I will be back...lol

mole999 · Jan 3, 2009

excel holds the date and time as 347858.5473673 Date before the decimal, time portion after the decimal, as 24hrs is always less than 1, so adding a whole 1 moves into the next day, though you know have me wondering if your cells are formatted as number and not time

dfournier · Jan 3, 2009

Yes...sorry. The cell where the number is allocated is formatted as a number. What I had to format as a {H}:mm is the drop down menu of times you can select to clock in and out.

mole999 said:
excel holds the date and time as 347858.5473673 Date before the decimal, time portion after the decimal, as 24hrs is always less than 1, so adding a whole 1 moves into the next day, though you know have me wondering if your cells are formatted as number and not time

barry houdini · Jan 3, 2009

dfournier said:
In the formula is I used 24:00 for midnight, 25:00 for 1am, 26:00 for 2am etc. it calculates it as it should...

If you mean using a formula like

=SUM(IF(C4:C16<="26:00"+0,IF(D4:D16>="21:00"+0,IF(D4:D16<"26:00"+0,D4:D16,"26:00")-IF(C4:C16>"21:00"+0,C4:C16,"21:00"))))*24

to calculate hours between 9 PM and 2 AM then that can work......depending on what sort of shifts you have.......

If you have a shift that finishes at 1 AM then you'll need to input that as 25:00 on the worksheet [you can format as hh:mm to display as 01:00]

...and if you had a shift that was, for instance, 01:00 to 09:00 then, in order to count that hour within the 9 PM to 2 AM time period, you'd have to enter it as 25:00 to 33:00....in which case the formula that counts hours between 05:00 and 10:30 won't count the 4 hours from that shift.
....

so that won't be a viable solution if you have shifts of both of those types

I think there's probably a better solution which dosen't involve entering times greater than 24:00......I'll try to get back to you.....

dfournier · Jan 3, 2009

Actually this works out really well the way I described it. The earliest anyone comes in is 5am

The latest anyone leaves is 2:30 am.

There is no over lap and so far using the 24:00, 25:00 and 26:00 in the formula, with it appearing as traditional times in the schedule itself has worked without a glitch this far. I still have a long way to finish this schedule and problem can appear at any time but without your help and the basis for the formula below I would not be in a position to finish this today, hopefully.

Thanks!

barry houdini said:
If you mean using a formula like

=SUM(IF(C4:C16<="26:00"+0,IF(D4:D16>="21:00"+0,IF(D4:D16<"26:00"+0,D4:D16,"26:00")-IF(C4:C16>"21:00"+0,C4:C16,"21:00"))))*24

to calculate hours between 9 PM and 2 AM then that can work......depending on what sort of shifts you have.......

If you have a shift that finishes at 1 AM then you'll need to input that as 25:00 on the worksheet [you can format as hh:mm to display as 01:00]

...and if you had a shift that was, for instance, 01:00 to 09:00 then, in order to count that hour within the 9 PM to 2 AM time period, you'd have to enter it as 25:00 to 33:00....in which case the formula that counts hours between 05:00 and 10:30 won't count the 4 hours from that shift.
....

so that won't be a viable solution if you have shifts of both of those types

I think there's probably a better solution which dosen't involve entering times greater than 24:00......I'll try to get back to you.....

I got a real tough problem with time formula's...anyone care to tackle it?

dfournier

New Member

dfournier

New Member

Excel Facts

mole999

Well-known Member

dfournier

New Member

barry houdini

MrExcel MVP

dfournier

New Member

Similar threads

Forum statistics

Share this page

I got a real tough problem with time formula's...anyone care to tackle it?

dfournier

New Member

dfournier

New Member

Excel Facts

mole999

Well-known Member

dfournier

New Member

barry houdini

MrExcel MVP

dfournier

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock