To calculate the difference between two times in hours as a decimal value, multiply the previous formula by 24 and change the number format to General.Ĭalculate total hours, regular hours and overtime hours with our timesheet calculator. Time Difference in Hours as Decimal Value If not, add 1 (full day) to the end time to represent a time on the next day and subtract the start time. The time difference formula below always works.Įxplanation: if the end time is greater than or equal to the start time, simply subtract the start time from the end time. To clearly see this, change the number format of the values in cell A2, B2 and C2 to General.ģ. You need a formula that returns a negative value.
With this number format code you can represent any number as a time difference.
They are available in Microsoft Excel and Open Office format.
The simple formula shown above doesn't work if the start time is before midnight and the end time is after midnight.ġ. An easy example would be to use HH:MM:SS (am/pm makes no sense for time differences) which tells the number formatter that you don't want to calcuate times modulo 24 hours. The calculations in the NOAA Sunrise/Sunset and Solar Position Calculators are based on. For example, 6:00 AM is represented as 0.25 (quarter through the day). If your date and time were derived from a 'UTC' POSIX clock in the first place, you are probably good to go, since 'UTC' POSIX clock doesnt account for leap seconds (and of course has no DST). When working with Apache OpenOffice Calc, you can limit the amount of characters in certain cells by setting the validity criteria. Note: times are handled internally as numbers between 0 and 1. If you are interested in milliseconds, you should probably use a time scale which has neither of these (so neither local time nor UTC are good choices). Change the number format of the values in cell A2, B2 and C2 to General.