If probelm with time fields

[Sharon Hague]

Hi

I have 2 colums which are formatted to time as follows: -

Column A Column B
08:30 17:00
08:31 16:58
08:25 17:05
00:00 00:00


In column C which is also formatted to time, I have tried to insert the following if function but am struggling with the results.

=IF(A1="00:00",0,IF(A1>"8:30",0,"08:30"-A1))

Using the above if function the results don't work for any cells in column A that are greater than 08:30 as it displays all ###### and then any cells that have 00:00 it displays 08:30.

I would appreciate anybodies help on this as its driving me mad.

[benishiryo]

hi Sharon Hague, something like that perhaps?
=IF(OR(A1=0,A1>TIME(8,30,)),0,TIME(8,30,)-A1)

[dredwolf]

expand the column size, ####### indicates the date value wont fit the cell size...
Hope this helps

[dredwolf]

Also, all dates/times in excel are referenced from 0/0/1900 00:00 that is equal to zero, even though it displays as 0/0/1900 00:00...

[Kevin UK]

Hi Sharon

Has column B anything to do with this!

Please Login or Register  to view this content.

[Sharon Hague]

Brill - that works a treat thank you. The colmn width didn't work as this wasn't the issue. Thanks for all you advise.

[dredwolf]

Glad you got a solution
However, the ######## in a cell does mean a date/time won't fit, if you expand the column, you might see what the problem is, rather than just saying that isn't the problem...
Hope this helps

[Kevin UK]

@ dredwolf

As Sharon points out, there was no problem!

[dredwolf]

My profuse apologies!
had it pointed out to me that a negative time ALSO creates the ###### situation, just had never seen it before...again, I apologize