Announcement

Collapse
No announcement yet.

Calculate Day Difference From 2 Dates

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculate Day Difference From 2 Dates

    Hi Guys

    is anyone aware of a formula/macro that can calculate (in days, hours and minutes) how much time has elapsed between two date and time stamps?

    For example [30/07/2008 00:30] and [01/08/2008 01:45], so in days hours and minutes it would look somewhat like this: 02:01:15

    Cheers

  • #2
    Re: Calculating Time Elapsed Between Two Date And Time Stamps

    Hi

    Something wrong with just:

    =B1-A1

    if end is in B1 and start in A1?

    Richard

    Comment


    • #3
      Re: Calculating Time Elapsed Between Two Date And Time Stamps

      sorry i thought it would be that easy too...

      however probably my example wasnt the best. That formula is ok if the dates are within a few days of each other, but if they are over a month apart, it wont calculate correctly and we need to calculate in days for our reporting. so we will need to calculate:

      [30/07/2008 12:30:00 AM] and [28/09/2008 1:45:00 AM] The formula will only count up to 30 days and we require it to go above that. Is that possible?

      Comment


      • #4
        Re: Calculating Time Elapsed Between Two Date And Time Stamps

        I don't understand I'm afraid - the formula will do for any 2 date/time values used in Excel.

        The formatting of the result is another issue altogether

        Comment


        • #5
          Re: Calculating Time Elapsed Between Two Date And Time Stamps

          oh. sorry it may be a formatting issue.... if so then i am not sure how to make it count in just days not months

          [30/07/2008 12:30:00 AM] [1/09/2008 12:30:00 AM] with the above formula would be: 02:00:00 as the day value will only go up to 31

          for that example i need it to say: 33:00:00.

          Comment


          • #6
            Re: Calculating Time Elapsed Between Two Date And Time Stamps

            As you've learned, Excel considers d or dd to be the day of the month, and so won't exceed 31. You can do this, but the result will be text:

            =INT(B1-A1) & TEXT(B1-A1, ":hh:mm")
            Entia non sunt multiplicanda sine necessitate.

            Comment


            • #7
              Re: Calculating Time Elapsed Between Two Date And Time Stamps

              Do you mean


              Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
               
               =Day(B1)-Day(A1) 

              Comment


              • #8
                Re: Calculate Day Difference From 2 Dates

                =INT(B1-A1) & TEXT(B1-A1, ":hh:mm")

                the result worked perfectly.
                thank you to everyone else for their help too!!

                cheers

                Comment

                Working...
                X