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 ...
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Sep 15, 2020

    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.

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Sep 14, 2020
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
Sep 14, 2020

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
    Sep 15, 2020

    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

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Sep 14, 2020
    Deutsch: Wenn Sie das hilfreich finden, markieren Sie es bitte als «Beste Antwort» und als Gefällt mir (klicken Sie den Daumen..Daumen hoch), es wird für mehr Community-Mitglieder nützlich sein, es als Lösung zu erkennen.

    English: If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

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

Resources