How to add formulas together, hours & minutes function.

[Rob S]

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]

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]

The correct result is 9.583333333


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,"")


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

 

[T. Valko]

You're welcome. Thanks for the feedback!

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

 

[Bernd]

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

 

[T. Valko]

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]

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

 

[Rob S]

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

 

[T. Valko]

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