Time calculations and formatting a whole number as minutes

English
#1

I’m doing some time calculations to determine minutes between 2 times. The times are entered in AM/PM format. That is: HH:MM:SS AM (or PM). The result comes out as HH:MM:SS. No AM or PM. Perfect. In another column I enter a whole number such as 18 and when I select the formatting for time it turns it into 06:00 PM. I’d like to be able to enter this column of whole numbers, select a time format of some sort, and then compare this data to another column that is in minutes.

In doing it manually I can get it to work one by one. Usually formatting in MM:SS. I’d like to be able to do the entire column at once. Either before the data is entered or after.

#2

This can’t happen at all. A whole number 18 is a specific date of “epoch plus 18 days, 0 hours 0 minutes 0 seconds”, in most common case 1900-01-17 00:00:00 exactly, but in all possible cases it would have 00:00:00 as time part.

The only possible case would be, if your “another column” already had not whole numbers, but times (= fractions) formatted as HH.

1 Like
#3

As Mike Kaganski pointed out, the important thing to remember is that time is a fraction of a day. There are 24 hours in a day so if you enter 18 in cell A1, you need another helper cell to convert that to 6:00 PM by dividing 18 by 24 hours, that equals 0.75 of a day or 6pm.
.
For minutes, there are 60 minutes in an hour and 24 hours in a day. To enter a number of minutes, say 36 into cell B1, the helper cell has to divide the number of minutes (36) by 60 and again by 24, or 36/(60*24), or more simply 36/1440
.
Instead of helper cells you could do all the equations in the difference cell =A1/24-B1/(60*24) or simplified a little =A1/24-B1/1440
.
If your hours have any possibility of exceeding 24 then you should format the cell(s) as [HH]:MM:SS otherwise only the excess over 24, or multiple of that, will be displayed and full days will not be visible
TimeCalculations.ods (13.7 KB)

#4

Thanks for the reply. Just want to let you know I read it. I’ll return to this next week.

#5

Thanks for the details and the sample spreadsheet. I’ll work through this next week and follow up.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.