asked on # 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

EE_time_format_not_working.xlsx

Microsoft ExcelMicrosoft Office

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

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

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
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.

When I downloaded the original the cells were formatted as time, I've downloaded it again and they still show as time to me.

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.

If you simply put

Same way when you enter

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?

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

I have downloaded the file again and added a formula =D7-B7 which works without any modifications

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.

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.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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.

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.

Your help has saved me hundreds of hours of internet surfing.

fblack61

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.

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.

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¬

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.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

How have the numbers been calculated/entered in I1?