Sorry, I misunderstood... I didn't download your file, I only looked at your picture. If I understand correctly, you want to retrieve the total number of hours. If that is correct, you could use this formula...
Sorry, I misunderstood... I didn't download your file, I only looked at your picture. If I understand correctly, you want to retrieve the total number of hours. If that is correct, you could use this formula...
HI E5 - E18 has a IF formula working out the hours worked. E19 adds these up =SUM(E5:E18) but i need the result to be a solid number so not 9:00 but 9. This is because E21 has a sum e20 x e19
HI E5 - E18 has a IF formula working out the hours worked. E19 adds these up =SUM(E5:E18) but i need the result to be a solid number so not 9:00 but 9. This is because E21 has a sum e20 x e19
I am still not clear on your actual need here, but let me take a guess. If you want the SUM formula in cell E19 (which I am guessing is summing a set of individual time values) to report the whole number of hours, then change it to this...
=TEXT(SUM(E5:E18),"[h]")
The key here is for you to put whatever has or is returning the time value in for what I have highlighted in red.
I am still not clear on your actual need here, but let me take a guess. If you want the SUM formula in cell E19 (which I am guessing is summing a set of individual time values) to report the whole number of hours, then change it to this...
=TEXT(SUM(E5:E18),"[h]")
The key here is for you to put whatever has or is returning the time value in for what I have highlighted in red.
Q1 When i enter a time into C5 the start time column. E5 =IF(D5<C5,D5+1,D5)-C5 populates with an answer.
I would only like E5 to report the answer when C5 Start Time & D5 Finish Time have been populated.
Q2. How can make the cells c5-18 d5-18 add a mask so the user has to add the time in the correct format 9:00 AM or 9:00pm not 21:00 hrs?