Hi Group,

Please could you give me an all singing, all dancing formula, or an
explanation of how to put these together.

A1 B1
23:00 07:30 =IF(B1<A1,B1+1,B1)-A1

This gives me the hours and minutes 8:30 in C1 (Format/Custom/HH:MM)
One hour needs to be added so... =C1+01:00 gives me 09:30 in D1
(Format/Custom/HH:MM)
Then I need it dislayed in decimal format =D1*24 which gives me 9.5 in
E1(Format/General)

It's not actually set out like that A1 B1 C1 D1, with the result I need in
E1, but it makes it easier to explain.

It's not urgent, as I already have the calculator working, I'd just like to
use this function elsewhere :-)


Next on the list is how to round up and down, for when the hours are not as
straightforward, I round them to the nearest 1/4 of an hour, for example

23:10 07:45 = 9.666666667 (It should be 9.5 by using 23:15 07:45)
21:05 11:30 = 15.41666667 (It should be 15.5 by using 21:00 11:30)


Can all of this be put in one formula?

These functions will be used on a spreadsheet and the final values copied
across to another spreadsheet, so it doesn't matter if extra columns need to
be created or hidden.


Can you help?

Cheers

Rob

>23:10 07:45 = 9.666666667

The correct result is 9.583333333

> 23:10 07:45.....(It should be 9.5 by using 23:15 07:45)
> 21:05 11:30.....(It should be 15.5 by using 21:00 11:30)

Instead of rounding the times, round the final result.

Try this:

=IF(COUNT(A1:B1)=2,ROUND((B1-A1+(B1<A1)+1/24)*24/0.25,0)*0.25,"")

> One hour needs to be added

That's what this is doing: +1/24

Biff

"Rob S" <(E-Mail Removed)> wrote in message
news:x_Syh.8360$(E-Mail Removed)...
> Hi Group,
>
> Please could you give me an all singing, all dancing formula, or an
> explanation of how to put these together.
>
> A1 B1
> 23:00 07:30 =IF(B1<A1,B1+1,B1)-A1
>
> This gives me the hours and minutes 8:30 in C1 (Format/Custom/HH:MM)
> One hour needs to be added so... =C1+01:00 gives me 09:30 in D1
> (Format/Custom/HH:MM)
> Then I need it dislayed in decimal format =D1*24 which gives me 9.5 in
> E1(Format/General)
>
> It's not actually set out like that A1 B1 C1 D1, with the result I need in
> E1, but it makes it easier to explain.
>
> It's not urgent, as I already have the calculator working, I'd just like
> to use this function elsewhere :-)
>
>
> Next on the list is how to round up and down, for when the hours are not
> as straightforward, I round them to the nearest 1/4 of an hour, for
> example
>
> 23:10 07:45 = 9.666666667 (It should be 9.5 by using 23:15 07:45)
> 21:05 11:30 = 15.41666667 (It should be 15.5 by using 21:00 11:30)
>
>
> Can all of this be put in one formula?
>
> These functions will be used on a spreadsheet and the final values copied
> across to another spreadsheet, so it doesn't matter if extra columns need
> to be created or hidden.
>
>
> Can you help?
>
> Cheers
>
> Rob
>

T. Valko wrote:
>> 23:10 07:45 = 9.666666667
>
> The correct result is 9.583333333
>
>> 23:10 07:45.....(It should be 9.5 by using 23:15 07:45)
>> 21:05 11:30.....(It should be 15.5 by using 21:00 11:30)
>
> Instead of rounding the times, round the final result.
>
> Try this:
>
> =IF(COUNT(A1:B1)=2,ROUND((B1-A1+(B1<A1)+1/24)*24/0.25,0)*0.25,"")
>
>> One hour needs to be added
>
> That's what this is doing: +1/24
>
> Biff

Cheers Biff, that works perfectly. I just used it on a s/shet I'd already
done and it matched my figures every time. This'll save me a few hours a
day... to do the other work!

Rob


>
> "Rob S" <(E-Mail Removed)> wrote in message
> news:x_Syh.8360$(E-Mail Removed)...
>> Hi Group,
>>
>> Please could you give me an all singing, all dancing formula, or an
>> explanation of how to put these together.
>>
>> A1 B1
>> 23:00 07:30 =IF(B1<A1,B1+1,B1)-A1
>>
>> This gives me the hours and minutes 8:30 in C1 (Format/Custom/HH:MM)
>> One hour needs to be added so... =C1+01:00 gives me 09:30 in D1
>> (Format/Custom/HH:MM)
>> Then I need it dislayed in decimal format =D1*24 which gives me 9.5
>> in E1(Format/General)
>>
>> It's not actually set out like that A1 B1 C1 D1, with the result I
>> need in E1, but it makes it easier to explain.
>>
>> It's not urgent, as I already have the calculator working, I'd just
>> like to use this function elsewhere :-)
>>
>>
>> Next on the list is how to round up and down, for when the hours are
>> not as straightforward, I round them to the nearest 1/4 of an hour,
>> for example
>>
>> 23:10 07:45 = 9.666666667 (It should be 9.5 by using 23:15 07:45)
>> 21:05 11:30 = 15.41666667 (It should be 15.5 by using
>> 21:00 11:30) Can all of this be put in one formula?
>>
>> These functions will be used on a spreadsheet and the final values
>> copied across to another spreadsheet, so it doesn't matter if extra
>> columns need to be created or hidden.
>>
>>
>> Can you help?
>>
>> Cheers
>>
>> Rob

You're welcome. Thanks for the feedback!

Biff

"Rob S" <(E-Mail Removed)> wrote in message
news:Aj%yh.8544$(E-Mail Removed)...
> T. Valko wrote:
>>> 23:10 07:45 = 9.666666667
>>
>> The correct result is 9.583333333
>>
>>> 23:10 07:45.....(It should be 9.5 by using 23:15 07:45)
>>> 21:05 11:30.....(It should be 15.5 by using 21:00 11:30)
>>
>> Instead of rounding the times, round the final result.
>>
>> Try this:
>>
>> =IF(COUNT(A1:B1)=2,ROUND((B1-A1+(B1<A1)+1/24)*24/0.25,0)*0.25,"")
>>
>>> One hour needs to be added
>>
>> That's what this is doing: +1/24
>>
>> Biff
>
> Cheers Biff, that works perfectly. I just used it on a s/shet I'd already
> done and it matched my figures every time. This'll save me a few hours a
> day... to do the other work!
>
> Rob
>
>
>>
>> "Rob S" <(E-Mail Removed)> wrote in message
>> news:x_Syh.8360$(E-Mail Removed)...
>>> Hi Group,
>>>
>>> Please could you give me an all singing, all dancing formula, or an
>>> explanation of how to put these together.
>>>
>>> A1 B1
>>> 23:00 07:30 =IF(B1<A1,B1+1,B1)-A1
>>>
>>> This gives me the hours and minutes 8:30 in C1 (Format/Custom/HH:MM)
>>> One hour needs to be added so... =C1+01:00 gives me 09:30 in D1
>>> (Format/Custom/HH:MM)
>>> Then I need it dislayed in decimal format =D1*24 which gives me 9.5
>>> in E1(Format/General)
>>>
>>> It's not actually set out like that A1 B1 C1 D1, with the result I
>>> need in E1, but it makes it easier to explain.
>>>
>>> It's not urgent, as I already have the calculator working, I'd just
>>> like to use this function elsewhere :-)
>>>
>>>
>>> Next on the list is how to round up and down, for when the hours are
>>> not as straightforward, I round them to the nearest 1/4 of an hour,
>>> for example
>>>
>>> 23:10 07:45 = 9.666666667 (It should be 9.5 by using 23:15 07:45)
>>> 21:05 11:30 = 15.41666667 (It should be 15.5 by using
>>> 21:00 11:30) Can all of this be put in one formula?
>>>
>>> These functions will be used on a spreadsheet and the final values
>>> copied across to another spreadsheet, so it doesn't matter if extra
>>> columns need to be created or hidden.
>>>
>>>
>>> Can you help?
>>>
>>> Cheers
>>>
>>> Rob
>
>

Hi Biff,

What is the correct result for
21:05 - 11:40
?

Your formula gives 15.5 but shouldn't it be 15.75?

15.75 would be our result if we round each input time, not just the
result:
=IF(COUNT(A1:B1)=2,1+(ROUND((B1+(B1<A1))*96,0)-ROUND(A1*96,0))/4,"")

Regards,
Bernd

>What is the correct result for 21:05 - 11:40?
>Your formula gives 15.5 but shouldn't it be 15.75?

I don't think so.

A1 = 21:05
B1 = 11:40

=B1-A1+(B1<A1)

returns: 14:35 (formatted as h:mm)

Add the hour (as per the op)

15:35

Rounded to the nearest 15m = 15:30

Converted to decimal = 15.5

Biff

"Bernd" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Biff,
>
> What is the correct result for
> 21:05 - 11:40
> ?
>
> Your formula gives 15.5 but shouldn't it be 15.75?
>
> 15.75 would be our result if we round each input time, not just the
> result:
> =IF(COUNT(A1:B1)=2,1+(ROUND((B1+(B1<A1))*96,0)-ROUND(A1*96,0))/4,"")
>
> Regards,
> Bernd
>

Hi Biff,

If Rob is happy then it's fine.
But if he has a time system or a rule which asks for rounded inputs
then it can differ from a rounded result.

Regards,
Bernd

Bernd wrote:
> Hi Biff,
>
> If Rob is happy then it's fine.
> But if he has a time system or a rule which asks for rounded inputs
> then it can differ from a rounded result.
>
> Regards,
> Bernd

Hi Bernd

> What is the correct result for 21:05 - 11:40?
> Your formula gives 15.5 but shouldn't it be 15.75?

The result would be 15.75 if the times had been 21:00 - 11:40. The times
are not required to be (in this case) *only* rounded up, it's just to the
nearest 1/4 hour, up *or* down. I see what you're getting at, but this
formula rounded them up and down exactly the way I would do it.

Great help this group isn't it! :-)

Cheers for the input

Rob

Just a thought.......

If you need to round the times *before* the calculation.....

Someone could "game" the system and get an extra 15m pay per day (based on
Bernd's scenario).

I know a lot of people that come up with ways to get over! One of the most
egregious and outrageous offenders I've ever seen went on to become a member
of upper management! If they only knew what this person was doing!

Biff

"Rob S" <(E-Mail Removed)> wrote in message
news:CiIzh.10000$(E-Mail Removed)...
> Bernd wrote:
>> Hi Biff,
>>
>> If Rob is happy then it's fine.
>> But if he has a time system or a rule which asks for rounded inputs
>> then it can differ from a rounded result.
>>
>> Regards,
>> Bernd
>
> Hi Bernd
>
>> What is the correct result for 21:05 - 11:40?
>> Your formula gives 15.5 but shouldn't it be 15.75?
>
> The result would be 15.75 if the times had been 21:00 - 11:40. The times
> are not required to be (in this case) *only* rounded up, it's just to the
> nearest 1/4 hour, up *or* down. I see what you're getting at, but this
> formula rounded them up and down exactly the way I would do it.
>
> Great help this group isn't it! :-)
>
> Cheers for the input
>
> Rob
>
>
>