In a Time Formula convert into Whole Number in same cell formula Struggling

[daleholden]

HI Guys

Please see attached image re problem I have question written on image

I have added the Spreadsheet here as well

PLease can you help

 

[Rick Rothstein]

If you Custom Format the cell using this Type pattern, you will see the number of hours over 24...

[h]:mm

 

[daleholden]

If you Custom Format the cell using this Type pattern, you will see the number of hours over 24...

[h]:mm

Hi Rick still does not help my formula in E21 i have 23 hours which should equal to PHP 2300 but it says PHP 96

 

[Rick Rothstein]

Hi Rick still does not help my formula in E21 i have 23 hours which should equal to PHP 2300 but it says PHP 96

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...

=TEXT(E21,"[h]")

 

[daleholden]

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...

=TEXT(E21,"[h]")

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

It currently is working this out wrong

 

[Rick Rothstein]

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

It currently is working this out wrong

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.

 

[daleholden]

HI Rick

That's great would you mind if i ask a few more questions about this sheet ?

 

[daleholden]

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?