Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Strange behavior of function

Status
Not open for further replies.

foundryqa

Technical User
Oct 17, 2001
162
US
I have the following function in several cells of an Excel worksheet. It returns results some of the time, but after certain events like copying the worksheet, it errors out to #value#. At that point I haven't changed formatting or values or anything, simply making a copy of the worksheet. Any ideas?

= TIME(HOUR(C1),MROUND(MINUTE(C1),30),0)+ ROUND(C1,0)

Thanks,

Fred
 
It is a hard value, but it is a literal, example:
'10/20/2003 2:30:00 AM

To be quite honest, I'm surprised the function worked in the first place, but when I go to format the field as a date, I get the same #value# error. The values are being sent in by vb. After an update of the data, boom it won't work any more.

Thanks,

Fred
 
Well that literal is NOT a date/time value. remove the tic and it is. I am surprised that the expression works too. Excel makes some unexpected assumptions about date/time values.

Skip,
Skip@TheOfficeExperts.com
 
As soon as the tick is removed, the error pops up. I have also gone in afterward to make sure the format gets set to date/time.

Fred

Could it have anything to do with the mround?
 
I think what what Excel does is, for instance
Hour(C1)
looks at the literal and concludes that this string looks like a date/time, so it converts

'10/20/2003 2:30:00 AM
to
37914.10417

then does the hour conversion of

.10417
to
2

which is INT(.10417 * 24)

:)

Skip,
Skip@TheOfficeExperts.com
 
Thanks Skip,

That could be true. I think I have found a better solution than my original function. It is:
=ROUND(C1*24/0.5,0)*0.5/24

That gives me a date/time value rounded to the nearest half hour. I'm guessing that I should have started the original post with what I was trying to do.

Thanks again,

Fred
 
Sorry,

C1 = 10/20/2003 4:45:00 AM

and I need it rounded to the nearest half an hour

10/20/2003 5:00:00 AM.

It looks like this new round function is working like a charm.

Fred
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top