calculate total hours/minutes from two time cells

[3Suk]

dear all,
I have below time values column A & B :
in A1 = 10:01
in B1 = 10:34
in A2 = 11:30
in B2 = 12:31

Please help to provide a formula which get the result as below :
C1 = 00:30 [round down from 00:33]
C2 = 01:00 [round down from 01:01]

Thanks,
PL.

 

[Claus Busch]

Hi,

Am Mon, 28 Jan 2013 02:01:24 -0800 (PST) schrieb 3Suk:

Please help to provide a formula which get the result as below :
C1 = 00:30 [round down from 00:33]
C2 = 01:00 [round down from 01:01]

what do you want to do?
Round down to the next full quarter of an hour, round down to next half
of an hour or to the next full 5 minutes?


Regards
Claus Busch

 

[Claus Busch]

hi,

Am Mon, 28 Jan 2013 02:01:24 -0800 (PST) schrieb 3Suk:

Please help to provide a formula which get the result as below :
C1 = 00:30 [round down from 00:33]
C2 = 01:00 [round down from 01:01]

round down to the next full 5 minutes:
=ROUNDDOWN((B1-A1)*288,0)/288
round down to the next full quarter of an hour:
=ROUNDDOWN((B1-A1)*96,0)/96
round down to the next full half hour:
=ROUNDDOWN((B1-A1)*48,0)/48


Regards
Claus Busch

 

[3Suk]

dear all,

I have below time values column A & B :

in A1 = 10:01

in B1 = 10:34

in A2 = 11:30

in B2 = 12:31



Please help to provide a formula which get the result as below :

C1 = 00:30 [round down from 00:33]

C2 = 01:00 [round down from 01:01]



Thanks,

PL.

Claus,
I want to calculate the hour/minutes of the two cells and round down in 30 minutes basis.

 

[3Suk]

dear all,

I have below time values column A & B :

in A1 = 10:01

in B1 = 10:34

in A2 = 11:30

in B2 = 12:31



Please help to provide a formula which get the result as below :

C1 = 00:30 [round down from 00:33]

C2 = 01:00 [round down from 01:01]



Thanks,

PL.

Claus,
I tried your formula, it worked. But a further query, please explain a bit of your formula, e.g. what does the "48" mean?
Great thanks,
Patrick

 

[Claus Busch]

Hi Patrick,

Am Mon, 28 Jan 2013 22:02:53 -0800 (PST) schrieb 3Suk:

I tried your formula, it worked. But a further query, please explain a bit of your formula, e.g. what does the "48" mean?

30 min = 1 Day / 48
If you will round down to full quarter:
15 min = 1 Day / 96


Regards
Claus Busch

 

[joeu2004]

round down to the next full 5 minutes:
=ROUNDDOWN((B1-A1)*288,0)/288
round down to the next full quarter of an hour:
=ROUNDDOWN((B1-A1)*96,0)/96
round down to the next full half hour:
=ROUNDDOWN((B1-A1)*48,0)/48

please explain a bit of your formula, e.g. what does
the "48" mean?

Claus simplified the arithmetic.

Excel time is stored as a decimal number of days. There are 24*60 = 1440
minutes in a day.

So to round down elapsed time to a multiple of n minutes, the general
formula is:

=ROUNDDOWN((B1-A1)*1440/n,0)*n/1440

That is, first we convert Excel time (B1-A1) to minutes by multiplying by
1440; then we compute the number of n-minute increments by dividing by n and
rounding down; then we compute the number of minutes by multiplying by n;
and finally, we compute the decimal number of days (Excel time) by dividing
by 1440.

For 30-minute increments, that becomes:

=ROUNDDOWN((B1-A1)*1440/30,0)*30/1440

Note that 1440/30 is 48; and 30/1440 is 1/48.

So that becomes:

=ROUNDDOWN((B1-A1)*48,0)*1/48

which is simply:

=ROUNDDOWN((B1-A1)*48,0)/48

 

[3Suk]

Claus, Joe,
Thanks for both detail explanations!

Patrick.