I am trying to figure out how I can subtract 2 times: day 1=7:20 am and day
2 = 14:45 answer should be 31:25. I need to use the result when averaging
the column. Formula used is : =IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"")
giving me 7:35 which would be correct if within 24 hrs. TIA

"Meebers" wrote in message
...
I am trying to figure out how I can subtract 2 times: day 1=7:20 am and
day 2 = 14:45 answer should be 31:25. I need to use the result when
averaging the column. Formula used is :
=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be
correct if within 24 hrs. TIA oops typo should be 7:25

If you have the date and time information together in the cells (such as
04/02/2008 07:20 and 05/02/2008 14:45), then instead of MOD(S50-N50,1) you
just want S50-N50.
If you don't have the date with the time, you just need to add 1 to the
result if you've got an extra day.

In either case, format the result as [h]:mm, not as h:mm
--
David Biddulph

"Meebers" wrote in message
...

"Meebers" wrote in message
...
I am trying to figure out how I can subtract 2 times: day 1=7:20 am and
day 2 = 14:45 answer should be 31:25. I need to use the result when
averaging the column. Formula used is :
=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be
correct if within 24 hrs. TIA oops typo should be 7:25

Thanks David....already tried as you suggested, however forgot about the
"[h]" part. Worked perfect. Thanks again.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
If you have the date and time information together in the cells (such as
04/02/2008 07:20 and 05/02/2008 14:45), then instead of MOD(S50-N50,1)
you just want S50-N50.
If you don't have the date with the time, you just need to add 1 to the
result if you've got an extra day.

In either case, format the result as [h]:mm, not as h:mm
--
David Biddulph

"Meebers" wrote in message
...

"Meebers" wrote in message
...
I am trying to figure out how I can subtract 2 times: day 1=7:20 am and
day 2 = 14:45 answer should be 31:25. I need to use the result when
averaging the column. Formula used is :
=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be
correct if within 24 hrs. TIA oops typo should be 7:25

Add the date in as well like:

Day 1
3/2/2008 7:20

Day 2
4/2/2008 14:45

(British style dates)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Meebers" wrote in message
...
I am trying to figure out how I can subtract 2 times: day 1=7:20 am and
day 2 = 14:45 answer should be 31:25. I need to use the result when
averaging the column. Formula used is :
=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be
correct if within 24 hrs. TIA

Too quick on the trigger.

Then custom format the cell as [h]:mm

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Add the date in as well like:

Day 1
3/2/2008 7:20

Day 2
4/2/2008 14:45

(British style dates)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Meebers" wrote in message
...
I am trying to figure out how I can subtract 2 times: day 1=7:20 am and
day 2 = 14:45 answer should be 31:25. I need to use the result when
averaging the column. Formula used is :
=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be
correct if within 24 hrs. TIA

Tx Sandy for the input....Tried the format as [hh]:mm as well since my eye
likes things "even" See no problems so far. I am using 07 and saving as
97-2003 since the folks at work have older versions. 07 complaining about
loss of formatting when saving to older version...will have to wait until
the morning and correct if necessary.

"Sandy Mann" wrote in message
...
Too quick on the trigger.

Then custom format the cell as [h]:mm

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Add the date in as well like:

Day 1
3/2/2008 7:20

Day 2
4/2/2008 14:45

(British style dates)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Meebers" wrote in message
...
I am trying to figure out how I can subtract 2 times: day 1=7:20 am and
day 2 = 14:45 answer should be 31:25. I need to use the result when
averaging the column. Formula used is :
=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be
correct if within 24 hrs. TIA