Time calculation formula not assessing correctly despite being formatted as [hh]:mm

I have a file here that basically assesses if the number of hours in Bank holiday is greater than the number of hours in I1. However, even though all the hours in the cells and in i2 have been formatted as [hh]:mm, the formula is not assessing correctly. For some reason the entry in I2 , which has been entered as 40:00 and formatted as [hh]:mm, is showing as 01/01/1900  16:00:00 in the formula bar.
EE_time_format_not_working.xlsx
LVL 1
agwalshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
It's not possible to open the file you've uploaded, seems to be a problem with the format.

How have the numbers been calculated/entered in I1?
0
agwalshAuthor Commented:
Will re-send the file. It is in xlsx format. The hours number has been entered as 40:00
EE_time_format_not_working.xlsx
0
Roy CoxGroup Finance ManagerCommented:
Is this better?

I've amended the formula in Bank Holiday, it doesn't appear that the formulas were referring to the correct cells originally. Also, it looks like the entry in I1 may have been incorrect. I changed it to 16:00, change it back if necessary
EE_time_format_not_working--1-.xlsx
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NorieAnalyst Assistant Commented:
The values in the time columns of the table are text.

So the first thing you need to do is convert them to 'real' time values.

You can do that like this:

1 Put 0 in a spare cell and copy it.

2 Select the 3 time columns in the table.

3 Right click, select Paste Special...  ,select Add in the Operation section and click OK.

4 With the 3 columns still selected right click, select Format cells... and set the format to [hh]:mm.

The calculation in the Bank Holiday should now be correct, though I think you might need to change D3 to D2 in the formula in E2.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Hi Norie

When I downloaded the original the cells were formatted as time, I've downloaded it again and they still show as time to me.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Norie is right. The time you see in the table are actually numbers as Text and Excel treats date and time as real numbers.

If you simply put 23:59 in any empty cell on the sheet, you will see 23:59:00 in the formula bar but as soon as you input 24:00, the formula bar will show 01/01/1900  00:00:00 because 24 hours means 1 day and 00 hours 00 minutes and 00 seconds. And 01/01/1900 means the number 1 behind the scene which you can confirm by changing the format of the cell where you entered 24:00 to General.

Same way when you enter 40:00 excel treats it as a time value and since it is greater than 24 hours, it shows 01/01/1900  16:00:00 in the formula bar as 40 hours is equal to 24 hours and 16 hours where 24 hours is equivalent to 1 day and that's why it shows 01/01/1900  16:00:00 in the formula bar.
0
agwalshAuthor Commented:
OK, so given all that. They are formatted as numbers with time - I get I can correct them with Norie's solution - and it works beautifully - thank you @Norie :-) .  Why would it be treating them as text? They *were* got as a download from an external system...would that explain it?
0
Roy CoxGroup Finance ManagerCommented:
That only occurred for me in I3. When I checked the format of the cells in the Table then the format was [hh]:mm.

I have downloaded the file again and added a formula =D7-B7 which works without any modifications
0
NorieAnalyst Assistant Commented:
agwalsh

How did you get the data from the download into Excel?

Roy

I checked using ISNUMBER and ISTEXT.

When you tried =D7-B7 Excel might have coerced the values to 'real' time values.
0
Rob HensonFinance AnalystCommented:
If its a repeated download from an external source and the values will be text you have two options:

1) Convert from text to a number as described earlier

2) Allow for text in the formula in column E:  
=IF(D2*1>$I$1, "Yes", "No")
or
=IF(TIMEVALUE(D2)>$I$1, "Yes", "No")

Multiplying by 1 forces Excel to evaluate the cell contents and it recognises it as a time so calculates accordingly.

TIMEVALUE function does the same but assesses the cell for all local time formats before calculating. However, if the cell contains an actual time rather than a text representation it throws an error. You can get round it with:
=IF(IFERROR(TIMEVALUE(D2),D2)>$I$1, "Yes", "No")

However, the first option with multiplying by 1 is simplest.
0
Roy CoxGroup Finance ManagerCommented:
Norie

I only tried that formula on a new download. before entering the formula  to test.

The original formulas were  checking the next row, I changed to a structured reference which seemed to fix the problem for my example workbook.
0
NorieAnalyst Assistant Commented:
Roy

When I downloaded the workbook there was a problem with the formula in E2, it referenced D3 instead of D2.

I tried changing it to use a structured reference but for whatever reason it didn't take.
0
agwalshAuthor Commented:
It was a sample file sent to me (external source) which I have anonymised. It has been weird but @Rob Henson's if formula to check if it's text or numbers would solve the problem - along with Norie's workaround as well. Thank you all - EE saves the day again¬
0
agwalshAuthor Commented:
I love how I got two good workable solutions for what is a very common problem in dealing with external data sources. Thanks again to all of you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.