Google-sheets – Decimal conversion of time values

formulasgoogle sheets

What is or can be done to add two cells, that return a time equation?

I deal with multiple time entries and have to enter them in a standard format of 4:45 hours or 4:25 hours. I have a decimal converter set up so it will convert it to 4.50, etc.

What I need is a formula that, with the 4 in one cell and the 45 in another, will feed a third cell with the number 4:45 which in turn can feed the fourth cell which is my decimal converter.

I would like it to work like http://www.springfrog.com/converter/decimal-time.htm .
That way I can just input the two separate numbers and get my converted total.

Best Answer

I think you need the time() function. e.g:

A1= hour B1 = minutes in C1, try:

=TIME(A1, B1, 0)

Related Solutions

Google-sheets – Time duration, when converted to decimal, shows negative

Google Sheets does recognize the date-time combination. This is what I get after entering the dates you gave in A1 and B1, and entering =B1-A1 in the cell C1:

+---+---------------------+--------------------+---------------+
|   |          A          |         B          |       C       |
+---+---------------------+--------------------+---------------+
| 1 | 10/27/2015 23:38:00 | 10/28/2015 1:38:00 | 0.08333333334 |
+---+---------------------+--------------------+---------------+

No error; the output is expected: the difference is 0.08333.. of the day, i.e., 2 hours. The column C should be formatted as Duration to make the output user friendly.

Then I tested addition; it also works as expected, with C4 being SUM(C1:C3).

+---+---------------------+--------------------+----------+
|   |          A          |         B          |    C     |
+---+---------------------+--------------------+----------+
| 1 | 10/27/2015 23:38:00 | 10/28/2015 1:38:00 | 2:00:00  |
| 2 | 10/27/2015 15:38:00 | 10/28/2015 4:38:00 | 13:00:00 |
| 3 | 10/27/2015 14:38:00 | 10/28/2015 2:38:00 | 12:00:00 |
| 4 |                     |                    | 27:00:00 |
+---+---------------------+--------------------+----------+

Conclusion: something went wrong at the stage of entering data in your sheet. Check whether 10/27/2015 is recognized (autoformatted as date) when you enter it in a cell. This behavior depends on your locale setting.

Google-sheets – Tally duration, client, client category in each Google sheets Timesheet

I'll outline the strategy in three steps. Some of them may be combined, but doing things step by step makes the process more manageable.

Import ranges

Start by pulling the data from assorted spreadsheets into one summary spreadsheet. For example, create a separate sheet/tab for each source, enter =importrange(..., "A:E") in A1, and something like

=arrayformula(if(A:A <> "", {"This_Client", "This_Category"}, ))

to mark the client and category for the source.

Combine data

In a summary sheet, enter

={filter(Sheet1!A:G, len(Sheet1!A:A)); filter(Sheet2!A:G, len(Sheet2!A:A)) }

to combine all the sources, excluding empty rows.

Query data

Use a query to summarize data and present selected columns.

=query(Summary!A:G, "select A, sum(B), F, G group by A, F, G")