Subtracting returns weird result?

[JonasTheGamer]

Hello!

First of all, thanks for making this kind of forum!

I have made (or am trying to make) a worksheet which allows me to fill in what I have done today (via a dropdown), put in how long I did it and my weight and then it calculates how much calories I've burnt!

That works perfectly fine :D I also have a few cells for adding what I ate and how much calories that were for each item.

I'm then using a basic SUM function twice to add all calories from both sides (one for the calories I've burnt, referenced to as "kcal out", once for the calories I've eaten, referenced to as "kcal in") and tried adding a subtract to get the total amount of calories I can still eat (or how much I've already eaten too much)

Now, as you can see in the screenshots, it looks like the formula (which seems correct?) is doing some weird things..

Screenshots attached below.

I can't seem to add the excel file as attachment so here is a onedrive link which you can use to view it (remove spaces from link)
1drv .ms/x/s!AiAQ1qgvEV2Al-sbDKzIdd2Tgwx20Q?e=1zYVnv

I've disabled editing permissions as I don't want anyone on the internet to edit the file, but feel free to take a copy and experiment with it.

Thanks in advance for taking time out of your day to assist me.

[Fluff13]

Hi & welcome to MrExcel.
Try changing the cell format of N6 to General.

[JonasTheGamer]

Hi! Thanks for prompt reply! I tried it, but then it changes the value to "2,193940972" which is not what I want haha.

I've already been debugging a little before posting the thread and found it that it "all goes wrong" at the moment where I subtract the times to get the duration ("duur") between the start and end time.

I made a custom format [m] there so I could get the time in minutes and calculate the kcal with it. Then I take the sum of all kcal fields. I tried changing the kcal format to "general" as well (E11) but instead of 510 it then gives me 0,39046875?

Weird.. What's the relation between these weird numbers and the actual numbers I need? For example if it was the "right" number divided by 10, I could just muliply by 10 again to fix my issue.

[Fluff13]

Try using this formula for col F

Please Login or Register  to view this content.

[JonasTheGamer]

That works, thanks! :D What does *1440 do? (Well, I know it multiplies haha, but I mean, why?)

[Fluff13]

You're working in minutes & xl stores time as a fraction of one day so 1 min is actually 0.000694444444444444 the 1440 is 60*24 ie 60 mins in an hour & 24 hours in a day.
So it's converting the time in minutes into a "real" number.