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

#### daleholden

##### Board Regular
HI Guys

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

#### Rick Rothstein

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

[h]:mm

#### daleholden

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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.

HI Rick

#### daleholden

##### Board Regular
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?

1,082,135
Messages
5,363,344
Members
400,729
Latest member
Lisa McConachy

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...