software

I have tried a number of the responses posted- sorry i can't seem to get it
right!
I want to calculate the number of days and hours between two date time values
e.g.

cell A1 start: 17/01/2006 16:12
cell A2 finish: 25/01/2006 10:00
cell A3 answer: 7 days 18 hrs

and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format rather
than 'general'?

thanks for your help-=INT(A2-A1)amp;quot; days quot;amp;ROUND(MOD(A2-A1,1)*24,0)amp;quot; hoursquot;

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;TBAquot; gt; wrote in message
...
gt; I have tried a number of the responses posted- sorry i can't seem to get
it
gt; right!
gt; I want to calculate the number of days and hours between two date time
values
gt; e.g.
gt;
gt; cell A1 start: 17/01/2006 16:12
gt; cell A2 finish: 25/01/2006 10:00
gt; cell A3 answer: 7 days 18 hrs
gt;
gt; and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format
rather
gt; than 'general'?
gt;
gt; thanks for your help-
gt;
Thanks Bob- such prompt service!
that works fine.=TEXT(INT(A2-A1),quot;#quot;)amp;quot; Days quot;amp;TEXT(24*(MOD(A2-A1,1)),quot;#quot;)amp;quot; hrs.quot;

this formula outputs the total number of days, and rounds the remaining
hours (18 hrs instead of 17 hrs 48 minutes).

You might be having trouble with the way you are inputing your dates. If
you format them to general you should get numbers like this.
38734.675
38742.41667
This is the actual serial number for the dates you specified. If they stay
in date format, that means the cells are actually text strings and not dates
(as far as excel is concerned). You might need to switch the day and month,
and then change the format to
dd/mm/yyyy hh:mm
to get them to show how you want.

quot;TBAquot; wrote:

gt; I have tried a number of the responses posted- sorry i can't seem to get it
gt; right!
gt; I want to calculate the number of days and hours between two date time values
gt; e.g.
gt;
gt; cell A1 start: 17/01/2006 16:12
gt; cell A2 finish: 25/01/2006 10:00
gt; cell A3 answer: 7 days 18 hrs
gt;
gt; and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format rather
gt; than 'general'?
gt;
gt; thanks for your help-
gt;

Bob,

you make me look so stupid . You posted and got a response in the time it
took me to finish writing my post.

Jason Rackley

quot;Bob Phillipsquot; wrote:

gt; =INT(A2-A1)amp;quot; days quot;amp;ROUND(MOD(A2-A1,1)*24,0)amp;quot; hoursquot;
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;TBAquot; gt; wrote in message
gt; ...
gt; gt; I have tried a number of the responses posted- sorry i can't seem to get
gt; it
gt; gt; right!
gt; gt; I want to calculate the number of days and hours between two date time
gt; values
gt; gt; e.g.
gt; gt;
gt; gt; cell A1 start: 17/01/2006 16:12
gt; gt; cell A2 finish: 25/01/2006 10:00
gt; gt; cell A3 answer: 7 days 18 hrs
gt; gt;
gt; gt; and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format
gt; rather
gt; gt; than 'general'?
gt; gt;
gt; gt; thanks for your help-
gt; gt;
gt;
gt;
gt;

thanks Sloth, i will try this too
cheers
Theo

quot;Slothquot; wrote:

gt; =TEXT(INT(A2-A1),quot;#quot;)amp;quot; Days quot;amp;TEXT(24*(MOD(A2-A1,1)),quot;#quot;)amp;quot; hrs.quot;
gt;
gt; this formula outputs the total number of days, and rounds the remaining
gt; hours (18 hrs instead of 17 hrs 48 minutes).
gt;
gt; You might be having trouble with the way you are inputing your dates. If
gt; you format them to general you should get numbers like this.
gt; 38734.675
gt; 38742.41667
gt; This is the actual serial number for the dates you specified. If they stay
gt; in date format, that means the cells are actually text strings and not dates
gt; (as far as excel is concerned). You might need to switch the day and month,
gt; and then change the format to
gt; dd/mm/yyyy hh:mm
gt; to get them to show how you want.
gt;
gt; quot;TBAquot; wrote:
gt;
gt; gt; I have tried a number of the responses posted- sorry i can't seem to get it
gt; gt; right!
gt; gt; I want to calculate the number of days and hours between two date time values
gt; gt; e.g.
gt; gt;
gt; gt; cell A1 start: 17/01/2006 16:12
gt; gt; cell A2 finish: 25/01/2006 10:00
gt; gt; cell A3 answer: 7 days 18 hrs
gt; gt;
gt; gt; and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format rather
gt; gt; than 'general'?
gt; gt;
gt; gt; thanks for your help-
gt; gt;


Here's another way....

=TEXT(ROUND((A2-A1)*24,0)/24,quot;d \da\y\s h \hr\squot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=505530The OP is spoilt. 47 minutes and three alternative solutions. Where else do
you get (free) service like that?

Bob

quot;Slothquot; gt; wrote in message
...
gt; Bob,
gt;
gt; you make me look so stupid . You posted and got a response in the time
it
gt; took me to finish writing my post.
gt;
gt; Jason Rackley
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =INT(A2-A1)amp;quot; days quot;amp;ROUND(MOD(A2-A1,1)*24,0)amp;quot; hoursquot;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;TBAquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I have tried a number of the responses posted- sorry i can't seem to
get
gt; gt; it
gt; gt; gt; right!
gt; gt; gt; I want to calculate the number of days and hours between two date time
gt; gt; values
gt; gt; gt; e.g.
gt; gt; gt;
gt; gt; gt; cell A1 start: 17/01/2006 16:12
gt; gt; gt; cell A2 finish: 25/01/2006 10:00
gt; gt; gt; cell A3 answer: 7 days 18 hrs
gt; gt; gt;
gt; gt; gt; and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format
gt; gt; rather
gt; gt; gt; than 'general'?
gt; gt; gt;
gt; gt; gt; thanks for your help-
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;