Time conversion help

[Nunya1]

Hi,

Hoping Toadstool will see this as he knows the history of what I have been trying to do. But if anyone can help I would be so grateful.

I am doing timesheets and have formulas in now that work for adding hours over multiple rows and then multiplying it by 1.5 and 2.

What I need now is I have it set up as below;

Start Time Break Finish time
08:00 1:00 17:00
09:00 00:30 17:00

With the break column if I type in 00:30 it works if I type :30 then it doesn't recognize it and the formulas surrounding it don't work or if I was to type in .5 it won't recognise that either.

I need to get the break time to convert when it is typed in into time format but even if I format the cells it isn't working. This should be so simple but it just isn't working for me. I am using Google Excel Sheets

Help would be appreciated

 

[Toadstool]

Hi Nunya1,

As I responded to your PM...

I'm sorry but I don't use Google Sheets (as I find it very slow) but I see two options:

1. Use decimal hours for the break (e.g. 15 minutes entered as .25) but then you'll need to change all your other calculations to convert that back to parts of an hour.

2. Select the smallest unit which can be offered for a break and do a Data Validation list offering those increments.
e.g. 00:15,00:30,00:45,01:00,01:15,01:30,01:45,02:00 and etc. if they may have long breaks.

 

[Nunya1]

Yeah I did see your response before thank you. I have just gone with the data validation that will be the easiest for everyone I think.

And finally I can say these fkn timesheets are done thank you it would have taken me at least 2 weeks to work this out by myself.