Calculating time without using time functions...

[Ian R]

Hi

I have made a spreadsheet to calculate the total amount of time spent on a
task by entering the start and end time.

I want to run the sheet on my mobile phone as it can run simple sheets but
it doesn't support the time functions therefore I have to calculate the
totals by entering the hours and minutes in separate columns.

I thought it was working OK and then discovered an error in calculating the
total minutes...

Example

Start Hr A1=10
Start Mins B1=20

End Hr A2=12
End Mins B2=0

Duration Hrs A3=A2-A1
Duration Mins B3=B2-B1

The problem with this is the duration minutes comes to -20 when it should be
20.

I've tried every which way I can think of to try and get round this but I'm
stumped!

Would appreciate any info on how to work around this...

Many thanks for your time ('scuse the unintended pun!)

Ian

 

[macropod]

Hi Ian,

Duration Hrs A3=INT(((A2-A1)*60+B2-B1)/60)
Duration Mins B3=MOD(((A2-A1)*60+B2-B1),60)

Cheers

 

[David Biddulph]

I don't know where you get the idea that duration minutes should be 20?
If your duration hours and minutes are trying to be the hours and minutes
between your start time and end time, then I would have expected 10h20m to
12h00m to be 1h40m?

Try:
A3=A2-A1-(B1>B2)
B3=MOD(B2-B1,60)

 

[Arvi Laanemets]

Hi

A3=24*(A1>A2)+A2-A1-(B1>B2)
B3=60*(B1>B2)+B2-B1


Arvi Laanemets

 

[Ian R]

Thanks Guys

I'm very grateful to you all taking the time to reply.

It now works like a dream and I'm learning how to count!

Cheers

Ian I^)