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

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
235
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
If you Custom Format the cell using this Type pattern, you will see the number of hours over 24...

[h]:mm
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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
Joined
Sep 22, 2002
Messages
235
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
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Sep 22, 2002
Messages
235
HI Rick

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

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
235
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?
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top