Time arithmetic

[JenniferMurphy]

How can I calculate the hours and minutes between a time and midnight? Suppose I have "10:47 pm" (22:47:00) in A1. What formula do I put in A2 to get the result "1:13", the hours and minutes from 10:47 pm to midnight?

I tried =24:00-A1, but that gets an error.

Thanks

 

[Tetra201]

Try

="24:00"-A1

 

[KOKOSEK]

="24:00:00"-A1

 

[Fluff]

Try
=1-A1

 

[JenniferMurphy]

Try

="24:00"-A1

That works. Thanks

Why does it need to be in quotes?

 

[JenniferMurphy]

="24:00:00"-A1

Thanks

 

[JenniferMurphy]

Try
=1-A1

That's an interesting solution. Thanks.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Tetra201]

... Why does it need to be in quotes?

A time value is a number in Excel; Excel converts 22:47 into a number when you enter it into a cell.
When you try to do arithmetic with strings, Excel tries to convert them into numbers as well:
="24:00"-"22:47" would return 0.050694... that can be formatted as h:mm to display 1:13
="2"+"2" would return 4
="2019-12-25"-"2019-11-26" would return 29, and so son.

 

[JenniferMurphy]

A time value is a number in Excel; Excel converts 22:47 into a number when you enter it into a cell.
When you try to do arithmetic with strings, Excel tries to convert them into numbers as well:
="24:00"-"22:47" would return 0.050694... that can be formatted as h:mm to display 1:13
="2"+"2" would return 4
="2019-12-25"-"2019-11-26" would return 29, and so son.

I did know that dates and times are just floating point numbers in units of days. If I enter "6:00", it gets stored as 0.25. But my question is why does "24:00"-A1 work, but 24:00-1 doesn't?