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

Hey Everyone!
I didn't intend to start arguements or bad feelings
among posters. I'm just trying to learn how this can be down so the guys on
our department can track their trades on our network. Sorry if this is
causing a problem

"JE McGimpsey" wrote:

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