Two problems to do with timesheets

[Emmett 001]

Good evening,

I have two issues as I will point out below.

1) I need to be able to change entered time in B4 and C4 into time from text they are entered as text because its 24hour time and couldn't get the zero to appear in front of the numbers unless entered as a time.
Pretty much the people entering the time enter it as is shown in the cells now but I want to conditional format it or something so it changes into a time format in the same cell.

2) and I am one hundred percent sure this has been asked and answered before, but I could not see it from a quick glance, I need E4 to show the time difference between B4 and C4 (even if they go over the midnight mark), in the case in the sample
you can see the wanted answers in column G.

I'll carry on looking for an answer in the mean time... knowing my luck it is something every simple that I am missing.

[bebo021999]

Try with time is inputed as "hhmm"

=(REPLACE(C4,LEN(C4)-1,,":")-REPLACE(B4,LEN(B4)-1,,":")+(B4-C4>0))*24

Format as general

Or

=(REPLACE(C4,LEN(C4)-1,,":")-REPLACE(B4,LEN(B4)-1,,":")+(B4-C4>0))

Format as time

[kvsrinivasamurthy]

Pl see file
In F4 then copied down

Please Login or Register  to view this content.