Forum Discussion

appletonthecat's avatar
appletonthecat
Copper Contributor
May 25, 2023
Solved

making times work

Hey People,   i am going around in circles and cannot seem to make this simple formula work   I have attached a copy of the table   I am trying to make a rota with the start time, finish time f...
excel
Formulas and Functions
office 365
  • NikolinoDE's avatar
    NikolinoDE
    May 25, 2023

    appletonthecat 

    On the fly a nested solution.

     

    Formula in English:

    =SUM(IF(E5<D5,((E5+1)-D5)*24,(E5-D5)*24),IF(G5<F5,((G5+1)-F5)*24,(G5-F5)*24),IF(I5<H5,((I5+1)-H5)*24,(I5-H5)*24),IF(K5<J5,((K5+1)-J5)*24,(K5-J5)*24),IF(M5<L5,((M5+1)-L5)*24,(M5-L5)*24),IF(O5<N5,((O5+1)-N5)*24,(O5-N5)*24),IF(Q5<P5,((Q5+1)-P5)*24,(Q5-P5)*24))

     

    Example in the file (only the first week has the formula).

     

    I hope that I could help you with this.

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
May 25, 2023

appletonthecat 

On the fly a nested solution.

 

Formula in English:

=SUM(IF(E5<D5,((E5+1)-D5)*24,(E5-D5)*24),IF(G5<F5,((G5+1)-F5)*24,(G5-F5)*24),IF(I5<H5,((I5+1)-H5)*24,(I5-H5)*24),IF(K5<J5,((K5+1)-J5)*24,(K5-J5)*24),IF(M5<L5,((M5+1)-L5)*24,(M5-L5)*24),IF(O5<N5,((O5+1)-N5)*24,(O5-N5)*24),IF(Q5<P5,((Q5+1)-P5)*24,(Q5-P5)*24))

 

Example in the file (only the first week has the formula).

 

I hope that I could help you with this.

  • appletonthecat's avatar
    appletonthecat
    Copper Contributor
    May 25, 2023
    hey, thanks so much this is exactly what i was trying to do

Resources