Forum Discussion

Sockens's avatar
Sockens
Copper Contributor
Sep 13, 2020
Solved

Calculating time between sheets

I have a sheet that receives all the inputs for our production including time started and time finished.

 

The times inserted can be from one day to the next.  I have not been able to subtract the times with the dates.  I need for it to calculate the time difference between an evening time to the next day evening time or morning time or whatever it might be.  I tried INT also and made sure the PM and AM were a part of the data and formatted cells.

  • Sockens 

    I agree with Riny_van_Eekelen 

    The safest option is to include the dates in the calculation of elapsed time

    = (endDate+endTime) - (startDate+startTime)
    
    = MOD(endTime -startTime, 1)

    The second option is only usable if you are certain the elapsed time will never exceed 24hrs.

    The number format you require is [h]:mm unless you choose to multiply by 24 and output the result as decimal hours, in which case it would be 0.0 "hours" .

     

    The ranges defined for your dried weight calculations are incorrect and include the total itself, so giving rise to circular references.

9 Replies

  • Sockens 

    I agree with Riny_van_Eekelen 

    The safest option is to include the dates in the calculation of elapsed time

    = (endDate+endTime) - (startDate+startTime)
    
    = MOD(endTime -startTime, 1)

    The second option is only usable if you are certain the elapsed time will never exceed 24hrs.

    The number format you require is [h]:mm unless you choose to multiply by 24 and output the result as decimal hours, in which case it would be 0.0 "hours" .

     

    The ranges defined for your dried weight calculations are incorrect and include the total itself, so giving rise to circular references.

    • Sockens's avatar
      Sockens
      Copper Contributor

      PeterBartholomew1 

      I saw your comment at the bottom and went in and checked, do not know how that happened, I know better then to do that and also didn't check that either because wouldn't have know it had executed it like that.  Made changes and it works.  Thank you.

       

      For the time, you are saying your equation will work if the time is more than 24 hours.  Which I have an occasional fruit that does go over 24 hours.

       

      Thank you.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Perhaps this information will help you a little.
    When A1 is starting hours and B1 is finishing hours.

    Englisch:
    IF(B1<A1,((B1+1)-A1)*24,(B1-A1)*24))

    German:
    WENN(B1<A1;((B1+1)-A1)*24;(B1-A1)*24))

    I would be happy to know if I could help.

    Nikolino
    I know I don't know anything (Socrates)

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

    • Sockens's avatar
      Sockens
      Copper Contributor

      It workedNikolinoDE I got 15 hours.

       

      Thank you.  This way I do not need to include the date.

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        Sockens Since you already have the dates in your schedule in C1 and D1, why not simply use

        =(D1+D5)-(C1+C4)

        and custom format as [h]:mm, just to be sure to calculate duration in stead of time. Result will be 15:10

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    Sockens I suspect you are looking at L21. You probably have the formula =L19-L17 there and you want to see 27:00 hours. In stead, you get 3:00. When you subtract dates and times, Excel defaults to a time format. Time can be presented from 00:00 up to 23:59 (=24 hours = 1 day). After that, time starts from 00:00 again. Hence, 27 hours becomes 3:00 , unless you explicitly tell Excel to continue adding hours. Time then becomes Duration. Use custom formatting like [h]:mm to display 27:00.

  • mtarler's avatar
    mtarler
    Silver Contributor

    Sockens  It is hard to know what the problem is without seeing it.  My suspicion is that there isn't a problem with the formula but in what you are expecting to get and what you are seeing.  If you subtract 2020-09-11 5:00pm from 2020-09-12 11:00pm you might expect 18:00 hrs but see 1.25 instead.  In excel dates are represented based on days so 1.25 means 1 1/4 days or 1 day 6 hrs.  There are ways to force excel to display using a particular number (or date or time) format but you haven't really explained what you need to see and what you are seeing.  Maybe include a sample sheet? 

Resources