function to calculate 'duration'. The difference between start date/time and end date/time
Author Message
function to calculate 'duration'. The difference between start date/time and end date/time

This is my first effort at posting to the group so here goes.......
I am looking for a function that will return the difference between a
starting date/time and the ending date/time and express the result as
number of days hours and minutes (2 days 6 hours 27 minutes).

A sample of what I need to accomplish is:
Start date/time....6/15/01 1:15pm
End date/time......6/17/01 3:30pm
Duration =         2 days 2 hours 15 minutes

My starting and ending columns currently show date and time in the
same cell (5/15/01 6:15 pm) but I could divide date and time into
separate cells if that would help.

I experimented with the Days360 function but it deals in days only and
not hours and minutes.

Any help would be appreciated.

Maurice Story

Sun, 11 Jan 2004 00:30:40 GMT
function to calculate 'duration'. The difference between start date/time and end date/time
Try this,

=DAY(B1-A1)&" days "&HOUR(B1-A1)&" hours "&MINUTE(B1-A1)&"
minutes"

replace B1-A1 with your own cell references, it's untested
but hopefully it'll work

Regards,

Peo Sjoblom

Quote:
>This is my first effort at posting to the group so here
goes.......
>I am looking for a function that will return the

difference between a
Quote:
>starting date/time and the ending date/time and express
the result as
>number of days hours and minutes (2 days 6 hours 27
minutes).

>A sample of what I need to accomplish is:
>Start date/time....6/15/01 1:15pm
>End date/time......6/17/01 3:30pm
>Duration =         2 days 2 hours 15 minutes

>My starting and ending columns currently show date and
time in the
>same cell (5/15/01 6:15 pm) but I could divide date and
time into
>separate cells if that would help.

>I experimented with the Days360 function but it deals in
days only and
>not hours and minutes.

>Any help would be appreciated.

>Maurice Story
>.

Sun, 11 Jan 2004 00:53:55 GMT
function to calculate 'duration'. The difference between start date/time and end date/time
Try using

A1: 6/15/01 1:15 pm
A2: 6/17/01 3:30 pm
A3: =A2 - A1

Format A3 by Format/Cells/Number/Custom

d "days" h "hours" mm "minutes"

Note the space inbetween the minute and am/pm - XL will only interpret a
string as a date if it falls into one of its standard date formats.

Quote:
> This is my first effort at posting to the group so here goes.......
> I am looking for a function that will return the difference between a
> starting date/time and the ending date/time and express the result as
> number of days hours and minutes (2 days 6 hours 27 minutes).

> A sample of what I need to accomplish is:
> Start date/time....6/15/01 1:15pm
> End date/time......6/17/01 3:30pm
> Duration =         2 days 2 hours 15 minutes

> My starting and ending columns currently show date and time in the
> same cell (5/15/01 6:15 pm) but I could divide date and time into
> separate cells if that would help.

> I experimented with the Days360 function but it deals in days only and
> not hours and minutes.

> Any help would be appreciated.

> Maurice Story

--

ROT13 encoding, decode for real mail

Sun, 11 Jan 2004 00:41:05 GMT
function to calculate 'duration'. The difference between start date/time and end date/time
Thanks for the quick response. Solution works just fine.
I guess I was trying to make it more difficult than it really was.

Maurice Story

Quote:

> Try using

> A1: 6/15/01 1:15 pm
> A2: 6/17/01 3:30 pm
> A3: =A2 - A1

> Format A3 by Format/Cells/Number/Custom

>    d "days" h "hours" mm "minutes"

> Note the space inbetween the minute and am/pm - XL will only interpret a
> string as a date if it falls into one of its standard date formats.

> > This is my first effort at posting to the group so here goes.......
> > I am looking for a function that will return the difference between a
> > starting date/time and the ending date/time and express the result as
> > number of days hours and minutes (2 days 6 hours 27 minutes).

> > A sample of what I need to accomplish is:
> > Start date/time....6/15/01 1:15pm
> > End date/time......6/17/01 3:30pm
> > Duration =         2 days 2 hours 15 minutes

> > My starting and ending columns currently show date and time in the
> > same cell (5/15/01 6:15 pm) but I could divide date and time into
> > separate cells if that would help.

> > I experimented with the Days360 function but it deals in days only and
> > not hours and minutes.

> > Any help would be appreciated.

> > Maurice Story

Sun, 11 Jan 2004 07:30:15 GMT

 Page 1 of 1 [ 6 post ]

Relevant Pages