attempting to enter and calculate time in office 365 sharepoint

I am trying to modify a spreadsheet in sharepoint (excel 365). It seems that many of the normal excel functions can't be used (IE: the format number function wont let me add a custom format so I am limited to what is there).
I also cannot figure out how to share the sheet here so I have added a sheet I created with the data I need. It seems simple enough, but nothing seems to work. (I cant access VBA so it will have to be done with formulas.)
Now the person has to enter their time with a colon (09:00- 14:00). I would like them to be able to enter the numbers without needing the colon.
I then need to have a tally of their hours.

PHP Code: 

Date    On Duty    Off Duty    Duty Hours
    3/1/2018    1200    1600       0.166666667
    3/2/2018    800    1700       0.375
    3/3/2018    800    1700    0.375
    3/4/2018    07:00 16:00    0
    3/5/2018            
    3/6/2018            
    3/7/2018            
    3/8/2018 


I must be doing something wrong. When I put that in F3 these are the numbers I get?
Juli

I tried a different approach, assuming I understand your problem correctly. As before, this goes in F3 and fill down.

Please Login or Register  to view this content.

-Jim

Jim,
That worked just fine! Thank You Kindly.
Juli

Or try:

=IF(E3<1,E3-D3,TEXT(E3,"00\:00")-TEXT(D3,"00\:00"))*24

Originally Posted by Juli

Jim,
That worked just fine! Thank You Kindly.
Juli

That's good to hear. Using the length function is kind of a kluge to make it work. But it's good enuff for govmt work!

This works well...for every line but #33 and #36? For some reason on every sheet (month) on line 33 and line 36 I get this #####. line 33 is the 28th of the month and line 36 is the 31st in months with 31 days. This makes no sense to me?

Originally Posted by Juli

This works well...for every line but #33 and #36? For some reason on every sheet (month) on line 33 and line 36 I get this #####. line 33 is the 28th of the month and line 36 is the 31st in months with 31 days. This makes no sense to me?

Hi Juli,

Do you have any sample values for lines 32 and 33?

Here is a screenshot

Eh...doesn't help. Maybe select those #### cells and tell us what it says in the top bar? Also do the same with #REF! cell. There's obviously some terror...I mean error ...in those #### cells.