Selected Answer

Excel cells have many poperties, among them the *Formula*, the *Value* and the *NumberFormat*. For example, if you enter =TODAY() in a cell Excel will know that it's a formula by the leading = sign and assign the string to the *Formula* property automatically. Then Excel will evaluate the formula and assign the result to the same cell's *Value* property. In this case, today, that is 43503. While Excel evaluates the TODAY() function it learns that the result must be date. Therefore a *NumberFormat* like *mm/dd/yyyy* will be set (depending upon your computer's Internationl settings), also automatically, and you may see 02/07/2019 displayed in the cell.

You can reverse the automation in each step. You might set a *NumberFormat* like *dddd* and display *Thursday* in place of the date. Or set the *NumberFormat* to *General* and see 43503. You might precede the formula's = sign with an apostroph, like *'=TODAY(),* and thereby cause the formula to become a text string which Excel will assign to the *Value* property and no longer evaluate.

Today is the 43503rd day since Jan 1, 1900. It follows that tomorrow will be represented by number 43504 and yesterday was 43502. =TODAY()+10 returns 43513 which can be displayed as whatever the *NumberFormat* property demands, and that is the entire "secret" of calculating dates in Excel.

Since one day is represented by the integer 1, it would follow that 0.5 should be half a day. In order to instill this notion with sense MS engineers determined that day 43503 should start at 00:00 and end at 24:00. Therefore any fraction added to 43503 must represent a time during that day. Logically, 43503.5 should be 12 noon. And it is.

Accordingly, if you apply a *NumberFormat* like *hh:mm:ss* to the cell containing the *=TODAY()* formula you will see 00:00:00 but *=TODAY() + 0.5* would display 00:12:00. Try *=TODAY() + 0.75* would display *00:18:00* or *00:06:00PM*. If you have *=TODAY()* in A1 and B1 *=A1* the two cells could have the same *Value* but display different things. You can also construct Date/Time *NumberFormat* like *mm/dd/yy hh:mm:ss*.

Since the interger 1 = 1 day, and one day = 24 hours, 1 hour must be 1 / 24. 1/60th of that (1 / 24 / 60) = one minute, and so on. Excel applies the conversions as part of the *NumberFormat*. You don't need to know or care how many threes there are in eight hours. The challenge you face is focused on data input. I have suggested that you use date/time numbers for all your times throughout your project. Therefore every time you enter must be converted to its appropriate date/time value at the point of entry.

Actually, Excel makes this task easy, again employing the *NumberFormat*. A cell formatted with *hh:mm:ss* will be assigned the correct *Value* automatically if you enter 8:00 or 8:00:00. Use Data Validation to reject entries like 8 or 8.00. Find a way (using hidden columns, perhaps, or VBA) to add the date to the user's entry.