I would like to know how to right a function to calculate time that spans
over 24 hours. Ex. StartTime 06:45 AM 06/17/06 EndTime 07:00 AM 06/18/06. I'm
new to this so take it slow thankyou

One way:

A1: 06:45 AM 06/17/06
A2: 07:00 AM 06/18/06

A3: =A2-A1

A3 returns 24:15 when formatted with

Format/Cells/Number/Custom [h]:mm

the []'s keep hours from rolling over at 24.

In article ,
Fast Learner wrote:

I would like to know how to right a function to calculate time that spans
over 24 hours. Ex. StartTime 06:45 AM 06/17/06 EndTime 07:00 AM 06/18/06. I'm
new to this so take it slow thankyou

or Format Cell as

dd "Days &" hh:mm "Mins"

to show as days and Mins

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562269

First, I'm not sure why you'd use hh:mm "Mins" since that will display
hours and minutes, not just minutes.


Second, what you're trying to do only works if the number of days are
<=31 AND you're using the 1900 Date system, since dd returns the day of
the month, not the number of days.

This APPEARS to work for a small number of days, since the 1900 date
system regards a value between 0 and 1 as day 0 (i.e., 0 January 1900 ==
31 December 1899).

However, you can see it fails when the number of days are 31 or if you
use the 1904 date system (0 = 1 January 1904, so dd for zero days
returns "01").



In article ,
VBA Noob
wrote:

or Format Cell as

dd "Days &" hh:mm "Mins"

to show as days and Mins

Put this in your pipe

yy "Years " mm "Months" dd "Days &" mm "Min


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562269

Did you actually try your format?

Aside from the problem with not working in the 1904 date system, the
second mm will return months, not minutes.

Additionally,

A1: 7/16/2006 06:45
A2: 7/17/2006 07:00
A3: =A2-A1

returns

00 Years 01 Months 01 Days & 01 Min

with your formatting in the 1900 date system, which is clearly one month
too many.


In article ,
VBA Noob
wrote:

Put this in your pipe

yy "Years " mm "Months" dd "Days &" mm "Min

Thanks to everyone!!!! I just Tried JE McGimpsey's function and it seems to
work find.

"JE McGimpsey" wrote:

One way:

A1: 06:45 AM 06/17/06
A2: 07:00 AM 06/18/06

A3: =A2-A1

A3 returns 24:15 when formatted with

Format/Cells/Number/Custom [h]:mm

the []'s keep hours from rolling over at 24.

In article ,
Fast Learner wrote:

I would like to know how to right a function to calculate time that spans
over 24 hours. Ex. StartTime 06:45 AM 06/17/06 EndTime 07:00 AM 06/18/06. I'm
new to this so take it slow thankyou