Elapsed Time over 24 hours

[Guest]

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

 

[JE McGimpsey]

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.

 

[VBA Noob]

or Format Cell as

dd "Days &" hh:mm "Mins"

to show as days and Mins

VBA Noo

 

[JE McGimpsey]

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").

 

[VBA Noob]

Put this in your pipe

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

 

[JE McGimpsey]

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.

 

[Guest]

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

 

[Guest]

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

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.

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