Time Calc > 24 hrs

[Meebers]

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]

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

 

[David Biddulph]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Meebers]

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

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

 

[Meebers]

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.

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk