simple calculating timesheet

How to make the billable time calculate in column E when entering start time in column C and ending time in column D? See image.

I want to do the time entries in column C and D using keyboard shortcuts if possible.
Designating AM and PM will matter when calculating the time difference between column D and column C.
When I enter time entries using ctrl+shift+; they appear correctly, but column E displays #VALUE!
I've also tried manual entry [2:45 PM etc.] with the same result.

I have tried D2-C2 [etc.] and =SUM(D2-C2) [etc.]
I've also tried changing the time format [format/cells/number/time].
I am using Excel 2000.
I'd like column E to add the billable hours entries and create a total thus: 12:45 [12 hours and 45 minutes]

All help greatly appreciated.

Gary

Pretty pdf file but less than useless for diagnosing formula issues.

Are you still using Excel 2000?


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

Still can't help without a file.

See: https://create.microsoft.com/en-us/s...ery=timesheets

Administrative Note:

Please attach your WIP practice worksheet as a worksheet, not a PDF.

Is your forum profile showing the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

The fundamentals of date and time entry are what you need to understand here.

It would have made much more sense to attach an Excel workbook than a PDF, by the way! I've had to recreate this for you - shouldn't have needed to do this.

Anyway, dates need to be entered in this format: m/d/yyyy (for your US locale).
Times need to be entered in this format: [h]h:mm (using the 24-hour clock, so 16:00 for 4pm)

Do NOT enter these in the format you want - instead, use custom number formats to make them display as you wish:

Date - mm dd yyyy
Time - h:mm AM/PM

See the attached - custom number formats have been applied to the date and time entry fields.

If you don't enter the dates and times correctly, Excel will see them as text and your basic calculations just won't work.