Time conversion help

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
974
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Sep 3, 2019
Messages
31
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 :biggrin: thank you it would have taken me at least 2 weeks to work this out by myself.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,527
Messages
5,523,393
Members
409,515
Latest member
chemitek

This Week's Hot Topics

Top