0

Can anyone help please?

When I sum two time value such as 09:00 and 22:30 I can get a result of 31:30 by formatting the cell using [h]:mm:ss.

However, whenever I try and do this with a subtotal row on an Excel table, it’s gives ridiculously large numbers?! For example, if I do the same as my first example, I get a result of something like 2million?!

Can anyone explain what I’m doing wrong? It works fine outside of the table but I need to use a table since I am populating the values from a PowerAutomate flow.

As above please see my example

Improve this question

1 Answer 1

Reset to default
1

Excel uses numbers to represent dates and times. It starts with 0.0 as midnight on Jan 1 1900 , 1.0 is midnight on Jan 2 1900 etc, each whole number is one day since Jan 1 1900 and times are represented using a fractional value. E.g. 0.5 is noon on Jan 1 1900, 2.25 is 6:00:00 am on Jan 3 1900 and so on.

When you sum date-times in Excel , it just sums these numerical values.

In your test case of adding two date-times 9:00am and 22:30 (=10:30pm) and getting 31:30, you are probably summing two fractional values only (e.g. 0.375 + 0.9375 ) to get a whole day and a bit ( = 1.3125) which the format you are using shows you 31:30.

Typically, date-times in Excel are both the whole number and the fraction. For recent dates these numbers can be quite large 44,926.5 is noon on Dec 31st 2022 for example. If you sum even a few of these, as numbers, and try to represent them as dates you will get an absurdly large number.

In order to help you better you will need to tell us the actually numerical value (not the formatted representation) of the underlying cells.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.