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  
 
 [ 6 post ] 

 Relevant Pages 

1. Calculate difference between Start Time & End Time

2. task duration and 'elapsed' time quantities

3. TIME: Calculating the difference between two date and time fields

4. Date/Time picker and calculating date differences

5. Time Duration for Start Time/Stop Time

6. Calculating an end date from a start date and elapsed time in MSExcel97

7. Formula to calculate end date from start date and elapsed time

8. calculate the difference between the last Start Date and the previous EndDate fo

9. Excel 5.0 Function to calculate time difference

10. Counting 'time elapsed' between dates/times


 
Powered by phpBB® Forum Software © phpBB Group