Counting Hours in a Schedule

[mike4777]

I was wondering if someone could help me develop a formula to count the hours when formatted in such a way:

Name	Hours	Monday	Tuesday	Wednesday	Thursday
Name 1	=Formula	8am-8pm	10:30pm-6:15am	etc	etc
Name 2
Name 3
etc

<tbody>
</tbody>

I can do it with a million LEft( and Right( but that seems terribly clunky and prone to possible error.

 

[Zack Barresse]

If you're not entering a date with each time, can you assume it will not be over 24 hours in a single shift? If so you must specify an end date with the end time, otherwise there is no way to interpret the logic.

In the case of 12pm as a start time and 8pm as an end time, subtraction indeed will work. F2-D2. End time minus start time. But if you do have hours which go past midnight, you'll need to use the date. Or at the very least assume that the date is always one day later for anything over midnight.

You don't need AM/PM in the cell [time] values, but if you don't use them you'll need to use military time. For example 5:00 PM would be 17:00. Thus 8:00 PM would be 20:00, or 20 hours, and 12:00 PM would be 12:00, or 12 hours, and 20-12 = 8. To use AM/PM correctly and not as text, you'll need a space between the time value. For example, 8:00AM is text, where as 8:00 AM is a time value.

If you assume one entry is always less than 24 hours, start times in column D, end times in column F, data starting in row 2, you could use simply...

=IF(F2<=D2,1,0)+F2-D2

 

[mike4777]

Here is what I have so far.

=((SUM(VALUE(IF(FIND(":", D3,1)=2, LEFT(D3,1), LEFT(D3,2))), IF(LEFT(RIGHT(D3,4),2)="15",0.25,IF(LEFT(RIGHT(D3,4),2)="30",0.5,IF(LEFT(RIGHT(D3,4),2)="45",0.75,IF(LEFT(RIGHT(D3,4),2)="00",0, "")))))) - (SUM(VALUE(IF(FIND(":",F3,1)=2, LEFT(F3,1), LEFT(F3,2))), IF(LEFT(RIGHT(F3,4),2)="15",0.25,IF(LEFT(RIGHT(F3,4),2)="30",0.5,IF(LEFT(RIGHT(F3,4),2)="45",0.75,IF(LEFT(RIGHT(F3,4),2)="00",0, "")))))))

This isolates the numbers and subtracts them.
But again,
12pm - 8pm does not equal 4 hours

 

[vogel997]

you need to put manually a space in between the number and "PM" e.g. 12 pm or use substitute function

<colgroup><col width="173" style="width: 130pt; mso-width-source: userset; mso-width-alt: 6326;"> <col width="110" style="width: 83pt;"> <col width="185" style="width: 139pt; mso-width-source: userset; mso-width-alt: 6765;"> <col width="179" style="width: 134pt; mso-width-source: userset; mso-width-alt: 6546;"> <col width="110" style="width: 83pt;"> <tbody> </tbody>

 

[Zack Barresse]

But again,
12pm - 8pm does not equal 4 hours

Read my post. And vogel997's. You need a space where you have none.

 

[mike4777]

If you're not entering a date with each time, can you assume it will not be over 24 hours in a single shift? If so you must specify an end date with the end time, otherwise there is no way to interpret the logic.

In the case of 12pm as a start time and 8pm as an end time, subtraction indeed will work. F2-D2. End time minus start time. But if you do have hours which go past midnight, you'll need to use the date. Or at the very least assume that the date is always one day later for anything over midnight.

You don't need AM/PM in the cell [time] values, but if you don't use them you'll need to use military time. For example 5:00 PM would be 17:00. Thus 8:00 PM would be 20:00, or 20 hours, and 12:00 PM would be 12:00, or 12 hours, and 20-12 = 8. To use AM/PM correctly and not as text, you'll need a space between the time value. For example, 8:00AM is text, where as 8:00 AM is a time value.

If you assume one entry is always less than 24 hours, start times in column D, end times in column F, data starting in row 2, you could use simply...

=IF(F2<=D2,1,0)+F2-D2

I didn't know that about the 8:00 pm (with a space). Interesting. But when I put that format in excel converts it to a number format which defeats the purpose of a schedule (unless there is some way to have it keep the "12:00 pm" format).

The dates are not programmed in there but that is an easy fix. I'll put that on row 2. Shifts will indeed go from 11pm - 7am so the date will have to be used as you say. I'm not sure how to do that.

 

[vogel997]

Try	=mod(f2-d2,1)
	For the graveyard shift use =mod(end time- start time,1)

<colgroup><col width="173" style="width: 130pt; mso-width-source: userset; mso-width-alt: 6326;"> <col width="110" style="width: 83pt;"> <col width="185" style="width: 139pt; mso-width-source: userset; mso-width-alt: 6765;"> <col width="179" style="width: 134pt; mso-width-source: userset; mso-width-alt: 6546;"> <col width="110" style="width: 83pt;" span="3"> <tbody> </tbody>

 

[mike4777]

what are these supposed to do and where do these go? The schedule still has the numeric values on it. (just so you know, the time boxes are drop down menu).

 

[Zack Barresse]

I gave you the formula. Basically the check logic is this:

If the end date is smaller than the start date, add a day, then subtract, otherwise just subtract the end time from the start time.

The cell format you want for the input cells is this (select the desired cells, then press CTRL+1):
h:mm AM/PM

For the output cell, you don't want AM/PM, and you want hours to accumulate, so the format would need to be:
[h]:mm

 

[mike4777]

this is not working. Somehow the value goes ############# when I type in 12:00am (it is formatted as you say).

 

[mike4777]

perhaps you can show me what you mean. I've posted my schedule program here: Scheduler (need to count hours)

I never double post to other sites except for this occasion so you can get the workbook.