0

I know that there are questions around that are much the same, like Sum the number of hours in Excel, which asks you to format [h]:mm, which I do and it still does not work. Also not so far is How do 'Sum' time in decimals for result in hours and minutes in Excel?.

I have an export of a timesheet from a timetracker Google Play app Work Time - Timesheet, in German "Zeiterfassung", which is further called "Arbeitszeittabelle Arbeitszeit" by "aadhk":

begin - end - break - working time ([h]:mm)

enter image description here

and I want to see the sum of the time in the status bar if I mark some of the "working time" cells, like the two 00:45 that should show 01:30 as the sum.

Yet, I can only see the number of cells in the status bar:

enter image description here

What is more, even if I sum the two cells up in a SUM formula, it returns 00:00:

enter image description here

enter image description here

If I write down the values in new cells, the formatting works, they are also aligned to the right, as they should:

enter image description here

Thus, the other numbers are understood as text even if I format them as [h]:mm, you see this from their alignment to the left.

How do I format the [h]:mm (hh:mm) cells so that I see the sum in the status bar, and how can I sum them up in a formula?

Improve this question
5
  • 1
    Step-by-step: Exactly how does the data get onto your Excel worksheet? Your data is text, which is why you are seeing the results you do. Simplest method would be to change the method of get the data onto the worksheet so it is seen as text. But I have an export of a timesheet doesn't explain how the data gets from this export onto your worksheet.
    – Ron Rosenfeld
    Commented Apr 13 at 21:38
  • I do not know how they do it. It is from a third party app. I cannot change it.
    – questionto42
    Commented Apr 13 at 21:39
  • It can't appear by magic. You must interact with something. Do you just go to your workstation and the worksheet is already open with data as you show?
    – Ron Rosenfeld
    Commented Apr 13 at 21:41
  • @RonRosenfeld You are right, I could write a mail to their support. I ask for an export as an Excel file, that is how it comes in. For now, I am fine with the answer below.
    – questionto42
    Commented Apr 13 at 22:01
  • Since it comes to you as an Excel file, there is not much you can do other than modify it after it gets to you. And the method shown in your accepted answer will work. Sometimes exports come as CSV files in which case the import method can make a difference.
    – Ron Rosenfeld
    Commented Apr 13 at 22:04

3 Answers 3

Reset to default
2

To convert time in text format to normal time (numeric) you have two choices.

1. Text to Columns (old way)

Data > Text to Columns - one column at a time

enter image description here

Output:

enter image description here

2. Office clipboard (new way)

Copy-paste to the same place using Office clipboard (not Windows clipboard):

Office clipboard

Paste to another place

Output when pasted at another place:

enter image description here

And then you can format the cells as [h]:mm since they will show up as a share in the 24 hours at first.

Paste in place (best choice)

Or you paste it at the same place, then it takes up the format that the column already had, [h]:mm.

After copying the range and clicking on the office clipboard, but before pasting in the same range:

enter image description here

After clicking to paste the range in place:

enter image description here

Improve this answer
3

Three things:

  • If the Status Bar shows a Count instead of a Sum, then the data is text, not numeric.
  • If a SUM() function returns 0, the input is text, not numeric.
  • The default number orientation is to the right of the cell, text is oriented to the left. In your screenshot, the cells are oriented to the left.

These all point to your data being text, not numbers.

You will need to make sure your data is numeric, formatted to show as time. If the data does not respond to number formatting, again, that is because it is text.

Format the values as General. Do they change? If not, you need to make sure that the data is entered in a format that your regional settings recognise as time values.

Improve this answer
2
  • If that resolved your issue, please mark the answer as described in the tour. If it didn't, please leave a comment, so I can follow up.
    – teylyn
    Commented Apr 13 at 21:16
  • How do I format it as numeric if the cells come in as text? I cannot change how the data comes in. I guess you began answering before I changed my question, sorry for that, since I saw right after opening the question that it is just this text format that leads to the missing sum in the status bar, as you write it here. Yet, I do not know how to change the format to Numeric. Formatting as General does not help.
    – questionto42
    Commented Apr 13 at 21:22
1

Make a new helper column and multiply all values with 1:

enter image description here

And the status bar and the formula work:

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .