Formula issue

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

I can add 9:30+9:30 will give me 19:00 (h.mm)
I try to add 9:30+9:30+9:30 gives me 4:30 (h:mm)

9:30 value is arrived from a formula
So I can add 2 values I am okay but try adding 3 values I
get the problem.
Start Time is 10:30 A.M C22
Finish Time is 8:00 P.M D22
Meal 0:30 E22

to give a total 9:30
=D22-C22+IF(C22>D22,1)*24-E22 the formula
I would like to add each day to total for the week (7 Days)

What do I need to do please to get this to work.
Thankyou
 
use custom format [hh]:mm will give you 28:30

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Hello from Steved
3:00 p.m. 12:25 a.m. 0:30 8:55 =B5-A5+IF(A5>B5,1)*24-E5
3:00 p.m. 12:25 a.m. 0:30 8:55 =B6-A6+IF(A6>B6,1)*24-E6
3:00 p.m. 12:25 a.m. 0:30 8:55 =B7-A7+IF(A7>B7,1)*24-E7
1682:45=[hh:mm]

I know by adding 8:55+8:55+8:55=26:45 [hh:mm]
but using the above Formula to add 8:55+8:55+8:55
I get 1682:45, so my question is how do I go about
subtracting 3:00 p.m. from 12:25 a.m. then minus 30 min
which will give me 8:55 please.

Thankyou




-----Original Message-----
use custom format [hh]:mm will give you 28:30

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hello from Steved

I can add 9:30+9:30 will give me 19:00 (h.mm)
I try to add 9:30+9:30+9:30 gives me 4:30 (h:mm)

9:30 value is arrived from a formula
So I can add 2 values I am okay but try adding 3 values I
get the problem.
Start Time is 10:30 A.M C22
Finish Time is 8:00 P.M D22
Meal 0:30 E22

to give a total 9:30
=D22-C22+IF(C22>D22,1)*24-E22 the formula
I would like to add each day to total for the week (7 Days)

What do I need to do please to get this to work.
Thankyou
Click to expand...


.
Click to expand...
 
Please refer to new post
-----Original Message-----
Hello from Steved
3:00 p.m. 12:25 a.m. 0:30 8:55 =B5-A5+IF(A5>B5,1)*24-E5
3:00 p.m. 12:25 a.m. 0:30 8:55 =B6-A6+IF(A6>B6,1)*24-E6
3:00 p.m. 12:25 a.m. 0:30 8:55 =B7-A7+IF(A7>B7,1)*24-E7
1682:45=[hh:mm]

I know by adding 8:55+8:55+8:55=26:45 [hh:mm]
but using the above Formula to add 8:55+8:55+8:55
I get 1682:45, so my question is how do I go about
subtracting 3:00 p.m. from 12:25 a.m. then minus 30 min
which will give me 8:55 please.

Thankyou




-----Original Message-----
use custom format [hh]:mm will give you 28:30

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hello from Steved

I can add 9:30+9:30 will give me 19:00 (h.mm)
I try to add 9:30+9:30+9:30 gives me 4:30 (h:mm)

9:30 value is arrived from a formula
So I can add 2 values I am okay but try adding 3 values I
get the problem.
Start Time is 10:30 A.M C22
Finish Time is 8:00 P.M D22
Meal 0:30 E22

to give a total 9:30
=D22-C22+IF(C22>D22,1)*24-E22 the formula
I would like to add each day to total for the week (7 Days)

What do I need to do please to get this to work.
Thankyou
Click to expand...


.
Click to expand...
.
Click to expand...
 
Steve,

Don 't know what is in column E, but you are adding 24 days, not 24 hours.
Either change the formula to

=B5-A5+IF(A5>B5,1)*1-E5

or better still

=MOD(B5-A5,1) - E5

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Steved said:
Hello from Steved
3:00 p.m. 12:25 a.m. 0:30 8:55 =B5-A5+IF(A5>B5,1)*24-E5
3:00 p.m. 12:25 a.m. 0:30 8:55 =B6-A6+IF(A6>B6,1)*24-E6
3:00 p.m. 12:25 a.m. 0:30 8:55 =B7-A7+IF(A7>B7,1)*24-E7
1682:45=[hh:mm]

I know by adding 8:55+8:55+8:55=26:45 [hh:mm]
but using the above Formula to add 8:55+8:55+8:55
I get 1682:45, so my question is how do I go about
subtracting 3:00 p.m. from 12:25 a.m. then minus 30 min
which will give me 8:55 please.

Thankyou




-----Original Message-----
use custom format [hh]:mm will give you 28:30

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hello from Steved

I can add 9:30+9:30 will give me 19:00 (h.mm)
I try to add 9:30+9:30+9:30 gives me 4:30 (h:mm)

9:30 value is arrived from a formula
So I can add 2 values I am okay but try adding 3 values I
get the problem.
Start Time is 10:30 A.M C22
Finish Time is 8:00 P.M D22
Meal 0:30 E22

to give a total 9:30
=D22-C22+IF(C22>D22,1)*24-E22 the formula
I would like to add each day to total for the week (7 Days)

What do I need to do please to get this to work.
Thankyou
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top