Re: How to calculate date and time

I am calculating the difference between two dates and times. For example,
6/3/2004 14:50 - 6/1/2004 12:00. The formula I am using now is
=text(a1-a2,"m, d, hh:mm"). When this formula calcuates the month
(especially in the example I just gave), the value for month is shown as one
more than what the answer should be. This is what is produces: 6/3/2004
14:50 - 6/1/2004 12:00 = 1, 2, 14:50. It should equal 0, 2, 14:50. The time
is important especially when calculation over a 24 hr period.

Any other ideas?

"Ron Rosenfeld" wrote:

> On Fri, 1 Jul 2005 13:36:16 -0700, "kasey" <kasey@xxxxxxxxxxxxxxxxxxxxxxxxx>
> wrote:
>
> >please help me in something
> >i have two cells:
> >31/10/2004 18:00
> >01/09/2004 07:00
> >i want to subtract the two cells
> >the result i am taking is 29/02/1900 11:00
> >but the correct result is 29 and one month
> >what can i do
> >thanks
>
> This is a common problem given that months (and years) have a variable number
> of days. So you have to decide how you want to represent certain values.
>
> For example, given dates:
>
> 31 Jan 2005
> 1 Mar 2005
>
> The Datedif function previously recommended will give an answer of
> 1 month -2 days
>
> I would think 1 month 1 day makes more sense.
>
> But, what about
>
> 28 Jan 2005
> 1 Mar 2005
>
> Do you want an answer of
> 1 month 1 day
> or
> 1 month 4 days
>
> The latter would be correct if you were counting full calendar months (February
> in this case) plus the days that were outside of full calendar months (28-31
> Jan + 1 March). But could give answers, under certain circumstances, of more
> than 31 days.
>
>
> --ron
>
.

Relevant Pages

  • Re: Losing textbox on table
    ... These two "small months" will be placed in one of the cells of the month. ... I repeat this process for all the months of the calendar. ... Dim dt As Date ... Dim tbl As Table ...
    (microsoft.public.word.tables)
  • RE: Find Date and Paste Data
    ... These cells are peoples names ... This worksheet is set up as a calendar and ultimately where I want to ... This makes it so as the Tracker sheet has new rows added the Ghost ...
    (microsoft.public.excel.programming)
  • Re: Calendar - conditional formatting?
    ... But your range X5:AC5 contains six cells. ... There are only 5 column headers of which is merged with Training ... "Mark Driscol" wrote: ... ='Training Calendar '! ...
    (microsoft.public.excel.programming)
  • Re: Calender Generation
    ... 'array' formula which means that you choose all of the cells you want it to ... If you ever have to edit an array formula, you must recommit it with the ... calendar for any month/year combination. ... this would all be handled by VBA code. ...
    (microsoft.public.excel.misc)
  • Worksheet Automation and Size Limiting
    ... These cells are peoples names ... This worksheet is set up as a calendar and ultimately where I want to ... populate the data from Tracker. ... This makes it so as the Tracker sheet has new rows added the Ghost sheet ...
    (microsoft.public.excel.worksheet.functions)
Loading