Hello,
I posted a question yesterday, but i cannot find my question- or any answer,
today.
So here we go again-
I have created a breakdown table for my companies roster. I have a simple
formula in the spreadsheet that tells me how many hours of a shift was on the
day rate (0530-1830) (24hr time), and how much is on the night rate
(1830-0530) ( security guards). for example I need the cell that has the
formula =SUM(G7-1830) (G7 being a time between 0530 and 1830), to display
zero, until i type a figure into G7.
Please help!
P.S- I'm not fluent with Excel so laymans terms would be appreciated.
thank you!!!!!

Hi Hello

You will need an IF statement in front of the SUM statement which will
force the answer to be dependent on the input (G7)

The syntax is IF(argument,true,false)

IF(g7="",0,sum(g7-1830))

So if G7 is empty, the formula will return nil (0), otherwise it will
return the answer of the sum (g7-1830)

Jon


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=466127

=IF(ISBLANK(G7);0;G7-TIMEVALUE("18:30"))

SUM is not necessary if you subtract a constant from a cell's value!

Regards,
Stefi


€žhello€� ezt Ã*rta:

Hello,
I posted a question yesterday, but i cannot find my question- or any answer,
today.
So here we go again-
I have created a breakdown table for my companies roster. I have a simple
formula in the spreadsheet that tells me how many hours of a shift was on the
day rate (0530-1830) (24hr time), and how much is on the night rate
(1830-0530) ( security guards). for example I need the cell that has the
formula =SUM(G7-1830) (G7 being a time between 0530 and 1830), to display
zero, until i type a figure into G7.
Please help!
P.S- I'm not fluent with Excel so laymans terms would be appreciated.
thank you!!!!!

Don't forget to format the result cell as "h:mm"!
Stefi


€žhello€� ezt Ã*rta:

Hello,
I posted a question yesterday, but i cannot find my question- or any answer,
today.
So here we go again-
I have created a breakdown table for my companies roster. I have a simple
formula in the spreadsheet that tells me how many hours of a shift was on the
day rate (0530-1830) (24hr time), and how much is on the night rate
(1830-0530) ( security guards). for example I need the cell that has the
formula =SUM(G7-1830) (G7 being a time between 0530 and 1830), to display
zero, until i type a figure into G7.
Please help!
P.S- I'm not fluent with Excel so laymans terms would be appreciated.
thank you!!!!!

Hello Hello,

With the start time in F7 and the finish time in G7 both entered as XL times
ie 04:30 etc. then:

=IF(OR(F7="",G7=""),"",24*MAX(MIN(G7,TIME(18,30,0) )-MAX(F7,TIME(5,30,0)),0))

will give the number of hours betwen 05:30 and 18:30

and shamelessly stealing Harlan Grove's formula:

=IF(OR(F7="",G7=""),"",24*((G7<=F7)*(1-TIME(5,30,0)+TIME(18,30,0))+MIN(TIME(
5,30,0),G7)-MIN(TIME(5,30,0),F7)+MAX(TIME(18,30,0),G7)-MAX(TIME(18,30,0),F7)
))

will give the number of hours outside of those hours. Both hours will
return the hours as a number not a time, (with the cell formated as
General). If you want it to remain as an XL time theb remove the 24* from
both formulas.


--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"hello" wrote in message
...
Hello,
I posted a question yesterday, but i cannot find my question- or any
answer,
today.
So here we go again-
I have created a breakdown table for my companies roster. I have a simple
formula in the spreadsheet that tells me how many hours of a shift was on
the
day rate (0530-1830) (24hr time), and how much is on the night rate
(1830-0530) ( security guards). for example I need the cell that has the
formula =SUM(G7-1830) (G7 being a time between 0530 and 1830), to display
zero, until i type a figure into G7.
Please help!
P.S- I'm not fluent with Excel so laymans terms would be appreciated.
thank you!!!!!

Thank you everyone of your assistance.
I cannot format the cells in to h:mm, as I am working with 24hour time which
looks like 1600 =4pm or 0230=2:30am so it has no ' : '. That's another hurdle
i'm come across. The other issue is that every day has to be broken down into
it's 24hours.
Meaning- if a guard works a shift from 1600-0600, the shift needs to be
broken down into 1600-1830 day rate, 1830-2400 night rate - next day
2400-0530 night rate then 0530-0600 day rate!
You can see my delima.
Please keep up the useful assistance
Regards,
--
thank you


"Sandy Mann" wrote:

Hello Hello,

With the start time in F7 and the finish time in G7 both entered as XL times
ie 04:30 etc. then:

=IF(OR(F7="",G7=""),"",24*MAX(MIN(G7,TIME(18,30,0) )-MAX(F7,TIME(5,30,0)),0))

will give the number of hours betwen 05:30 and 18:30

and shamelessly stealing Harlan Grove's formula:

=IF(OR(F7="",G7=""),"",24*((G7<=F7)*(1-TIME(5,30,0)+TIME(18,30,0))+MIN(TIME(
5,30,0),G7)-MIN(TIME(5,30,0),F7)+MAX(TIME(18,30,0),G7)-MAX(TIME(18,30,0),F7)
))

will give the number of hours outside of those hours. Both hours will
return the hours as a number not a time, (with the cell formated as
General). If you want it to remain as an XL time theb remove the 24* from
both formulas.

Hi hello,

Why do you make your task harder then necessary? Use "h:mm" format to
exploit Excel's full computing power! If you do so, the following method
solves your problem:

Place start of day (5:30) and start of night (18:30) in unused cells, e.g.
E2,G2.

A B C D E F
1 start end day night startday startnight
2

In C2: =Shifttime2(A2;B2;1;$E$2;$F$2)
In D2: =Shifttime2(A2;B2;2;$E$2;$F$2)

gives you the day and night hours. Do not forget to format columns A:F like
"h:mm"!

The Shifttime2 function:
Public Function Shifttime2(starttime As Date, endtime As Date, daytime As
Integer, _
startday As Date, startnight As Date)
Dim length As Variant
Dim timeday As Date, timenight As Date
If starttime endtime Then endtime = 1 + endtime
length = endtime - starttime
timeday = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0,
endtime - startnight)) _
+ WorksheetFunction.Max(0, endtime - (1 + startday))
timenight = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0,
startnight - starttime)) _
- WorksheetFunction.Max(0, endtime - (1 + startday))
Select Case daytime
Case 1
Shifttime2 = timeday
Case 2
Shifttime2 = timenight
Case Else
Shifttime2 = "Invalid daytime!"
End Select
End Function

It's possible to use cell formulas in columns C and D, but they are rather
lengthy:

In C2:
=MAX(0;(IF(A2B2;1+B2;B2)-A2)-MAX(0;IF(A2B2;1+B2;B2)-$F$2))+MAX(0;IF(A2B2;1+B2;B2)-(1+$E$2))

In D2:
=MAX(0;(IF(A2B2;1+B2;B2)-A2)-MAX(0;$F$2-A2))-MAX(0;IF(A2B2;1+B2;B2)-(1+$E$2))

Choose as you like!

Regards,
Stefi

€žhello€� ezt Ã*rta:

Thank you everyone of your assistance.
I cannot format the cells in to h:mm, as I am working with 24hour time which
looks like 1600 =4pm or 0230=2:30am so it has no ' : '. That's another hurdle
i'm come across. The other issue is that every day has to be broken down into
it's 24hours.
Meaning- if a guard works a shift from 1600-0600, the shift needs to be
broken down into 1600-1830 day rate, 1830-2400 night rate - next day
2400-0530 night rate then 0530-0600 day rate!
You can see my delima.
Please keep up the useful assistance
Regards,
--
thank you


"Sandy Mann" wrote:

Hello Hello,

With the start time in F7 and the finish time in G7 both entered as XL times
ie 04:30 etc. then:

=IF(OR(F7="",G7=""),"",24*MAX(MIN(G7,TIME(18,30,0) )-MAX(F7,TIME(5,30,0)),0))

will give the number of hours betwen 05:30 and 18:30

and shamelessly stealing Harlan Grove's formula:

=IF(OR(F7="",G7=""),"",24*((G7<=F7)*(1-TIME(5,30,0)+TIME(18,30,0))+MIN(TIME(
5,30,0),G7)-MIN(TIME(5,30,0),F7)+MAX(TIME(18,30,0),G7)-MAX(TIME(18,30,0),F7)
))

will give the number of hours outside of those hours. Both hours will
return the hours as a number not a time, (with the cell formated as
General). If you want it to remain as an XL time theb remove the 24* from
both formulas.

This is an improved Function that can handle periods up to 24 hours:

Public Function Shifttime2(starttime As Date, endtime As Date, daytime As
Integer, _
startday As Date, startnight As Date)
Dim length As Variant
Dim timeday As Date, timenight As Date
If starttime = endtime Then endtime = 1 + endtime
length = endtime - starttime
timeday = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0,
endtime - startnight)) _
+ WorksheetFunction.Max(0, endtime - (1 + startday)) _
- WorksheetFunction.Max(0, endtime - (1 + startnight))

timenight = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0,
startnight - starttime)) _
- WorksheetFunction.Max(0, endtime - (1 + startday)) _
+ WorksheetFunction.Max(0, endtime - (1 + startnight))
Select Case daytime
Case 1
Shifttime2 = timeday
Case 2
Shifttime2 = timenight
Case Else
Shifttime2 = "Invalid daytime!"
End Select
End Function

Regards,
Stefi


€žStefi€� ezt Ã*rta:

Hi hello,

Why do you make your task harder then necessary? Use "h:mm" format to
exploit Excel's full computing power! If you do so, the following method
solves your problem:

Place start of day (5:30) and start of night (18:30) in unused cells, e.g.
E2,G2.

A B C D E F
1 start end day night startday startnight
2

In C2: =Shifttime2(A2;B2;1;$E$2;$F$2)
In D2: =Shifttime2(A2;B2;2;$E$2;$F$2)

gives you the day and night hours. Do not forget to format columns A:F like
"h:mm"!

The Shifttime2 function:
Public Function Shifttime2(starttime As Date, endtime As Date, daytime As
Integer, _
startday As Date, startnight As Date)
Dim length As Variant
Dim timeday As Date, timenight As Date
If starttime endtime Then endtime = 1 + endtime
length = endtime - starttime
timeday = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0,
endtime - startnight)) _
+ WorksheetFunction.Max(0, endtime - (1 + startday))
timenight = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0,
startnight - starttime)) _
- WorksheetFunction.Max(0, endtime - (1 + startday))
Select Case daytime
Case 1
Shifttime2 = timeday
Case 2
Shifttime2 = timenight
Case Else
Shifttime2 = "Invalid daytime!"
End Select
End Function

It's possible to use cell formulas in columns C and D, but they are rather
lengthy:

In C2:
=MAX(0;(IF(A2B2;1+B2;B2)-A2)-MAX(0;IF(A2B2;1+B2;B2)-$F$2))+MAX(0;IF(A2B2;1+B2;B2)-(1+$E$2))

In D2:
=MAX(0;(IF(A2B2;1+B2;B2)-A2)-MAX(0;$F$2-A2))-MAX(0;IF(A2B2;1+B2;B2)-(1+$E$2))

Choose as you like!

Regards,
Stefi

€žhello€� ezt Ã*rta:

Thank you everyone of your assistance.
I cannot format the cells in to h:mm, as I am working with 24hour time which
looks like 1600 =4pm or 0230=2:30am so it has no ' : '. That's another hurdle
i'm come across. The other issue is that every day has to be broken down into
it's 24hours.
Meaning- if a guard works a shift from 1600-0600, the shift needs to be
broken down into 1600-1830 day rate, 1830-2400 night rate - next day
2400-0530 night rate then 0530-0600 day rate!
You can see my delima.
Please keep up the useful assistance
Regards,
--
thank you


"Sandy Mann" wrote:

Hello Hello,

With the start time in F7 and the finish time in G7 both entered as XL times
ie 04:30 etc. then:

=IF(OR(F7="",G7=""),"",24*MAX(MIN(G7,TIME(18,30,0) )-MAX(F7,TIME(5,30,0)),0))

will give the number of hours betwen 05:30 and 18:30

and shamelessly stealing Harlan Grove's formula:

=IF(OR(F7="",G7=""),"",24*((G7<=F7)*(1-TIME(5,30,0)+TIME(18,30,0))+MIN(TIME(
5,30,0),G7)-MIN(TIME(5,30,0),F7)+MAX(TIME(18,30,0),G7)-MAX(TIME(18,30,0),F7)
))

will give the number of hours outside of those hours. Both hours will
return the hours as a number not a time, (with the cell formated as
General). If you want it to remain as an XL time theb remove the 24* from
both formulas.