time problem

species8514

Board Regular
Joined
Oct 4, 2006
Messages
98
Hi guys

I have a time in a cell.. 00:04:20 and in the adjacent cell it basically says if the time in the cell is between 00:04:16 and 00:04:31, then return the number 7.

However what i am getting is #VALUE!

I have tried using floor.precise /1/24/60/60 to ensure it rounds down, but with no luck, and besides the time falls well within the range.

This forumlae is in about 100 cells, and most of them work fine. Its just a few exceptions dotted about where it doesnt.

Any ideas?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Something like this


Excel 2010
AB
100:04:207
Sheet9
Cell Formulas
RangeFormula
B1=IF(AND(A1<=TIME(0,4,31),A1>=TIME(0,4,16)),7,"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,584
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top