i have a problem when wanting to calculate time using 24 hour clock, i have
attached a simple example:

These are the columns & row 1:
A B C D
E
Start Finish Time of break Break length Hours
8:00 18:00 13:00 1.00 9.00
23:00 7:00 2:00 1.00 ERROR

I want to be able to use it for night time hours, the example that is there,
I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do
this?
The formula that i am using is as follows: Format of cells is 13:30
=IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1)

Try this instead of your formula:

=IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1)

Hope this helps.

Pete

mats wrote:
i have a problem when wanting to calculate time using 24 hour clock, i have
attached a simple example:

These are the columns & row 1:
A B C D
E
Start Finish Time of break Break length Hours
8:00 18:00 13:00 1.00 9.00
23:00 7:00 2:00 1.00 ERROR

I want to be able to use it for night time hours, the example that is there,
I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do
this?
The formula that i am using is as follows: Format of cells is 13:30
=IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1)

Pete,

That worked perfectly, but i have another problem on it, The night hours
needs to be calculated using a different rate, so i thought that i could have
2 totals, splitting the hours into the night rate & day rate, how can i do
this?

"Pete_UK" wrote:

Try this instead of your formula:

=IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1)

Hope this helps.

Pete

mats wrote:
i have a problem when wanting to calculate time using 24 hour clock, i have
attached a simple example:

These are the columns & row 1:
A B C D
E
Start Finish Time of break Break length Hours
8:00 18:00 13:00 1.00 9.00
23:00 7:00 2:00 1.00 ERROR

I want to be able to use it for night time hours, the example that is there,
I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do
this?
The formula that i am using is as follows: Format of cells is 13:30
=IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1)

Which hours do the night rate apply to, and which hours would be
classed as day rate?

Pete

mats wrote:
Pete,

That worked perfectly, but i have another problem on it, The night hours
needs to be calculated using a different rate, so i thought that i could have
2 totals, splitting the hours into the night rate & day rate, how can i do
this?

"Pete_UK" wrote:

Try this instead of your formula:

=IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1)

Hope this helps.

Pete

mats wrote:
i have a problem when wanting to calculate time using 24 hour clock, i have
attached a simple example:

These are the columns & row 1:
A B C D
E
Start Finish Time of break Break length Hours
8:00 18:00 13:00 1.00 9.00
23:00 7:00 2:00 1.00 ERROR

I want to be able to use it for night time hours, the example that is there,
I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do
this?
The formula that i am using is as follows: Format of cells is 13:30
=IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1)

Pete,

11pm to 7am are classed as the night rate.

tks

Mats



"Pete_UK" wrote:

Which hours do the night rate apply to, and which hours would be
classed as day rate?

Pete

mats wrote:
Pete,

That worked perfectly, but i have another problem on it, The night hours
needs to be calculated using a different rate, so i thought that i could have
2 totals, splitting the hours into the night rate & day rate, how can i do
this?

"Pete_UK" wrote:

Try this instead of your formula:

=IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1)

Hope this helps.

Pete

mats wrote:
i have a problem when wanting to calculate time using 24 hour clock, i have
attached a simple example:

These are the columns & row 1:
A B C D
E
Start Finish Time of break Break length Hours
8:00 18:00 13:00 1.00 9.00
23:00 7:00 2:00 1.00 ERROR

I want to be able to use it for night time hours, the example that is there,
I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do
this?
The formula that i am using is as follows: Format of cells is 13:30
=IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1)

Is anyone likely to work a combination of day and night hours at the
same time? For example, starting at 9pm and working through to 1am, or
starting 6am till 12 noon?

If not, then we could just use your start time column to determine a
"shift" - 1 for day, 2 for night, then use this to determine which rate
of pay should be applied to the hours.

Hope this helps.

Pete

mats wrote:
Pete,

11pm to 7am are classed as the night rate.

tks

Mats



"Pete_UK" wrote:

Which hours do the night rate apply to, and which hours would be
classed as day rate?

Pete

mats wrote:
Pete,

That worked perfectly, but i have another problem on it, The night hours
needs to be calculated using a different rate, so i thought that i could have
2 totals, splitting the hours into the night rate & day rate, how can i do
this?

"Pete_UK" wrote:

Try this instead of your formula:

=IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1)

Hope this helps.

Pete

mats wrote:
i have a problem when wanting to calculate time using 24 hour clock, i have
attached a simple example:

These are the columns & row 1:
A B C D
E
Start Finish Time of break Break length Hours
8:00 18:00 13:00 1.00 9.00
23:00 7:00 2:00 1.00 ERROR

I want to be able to use it for night time hours, the example that is there,
I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do
this?
The formula that i am using is as follows: Format of cells is 13:30
=IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1)

Hi

Standard durationformula for work past midnight -or not- is
=B1-A1+(B1<A1)
-I think they deserve a paid break working hours like that ;-)

HTH. Best wishes harald

"mats" skrev i melding
...
i have a problem when wanting to calculate time using 24 hour clock, i
have
attached a simple example:

These are the columns & row 1:
A B C D
E
Start Finish Time of break Break length Hours
8:00 18:00 13:00 1.00 9.00
23:00 7:00 2:00 1.00 ERROR

I want to be able to use it for night time hours, the example that is
there,
I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I
do
this?
The formula that i am using is as follows: Format of cells is 13:30
=IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1)