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

agwalsh
agwalsh used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?

Author

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
Roy CoxGroup Finance Manager

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Analyst 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.
Roy CoxGroup Finance Manager

Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

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

Author

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?
Roy CoxGroup Finance Manager

Commented:
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
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.
Rob HensonFinance Analyst
Commented:
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.
Roy CoxGroup Finance Manager

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

Author

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¬

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial