Strange behavior of function

[foundryqa]

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

 

[SkipVought]

Hi,

Is there a hard number in C1 or a reference?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[foundryqa]

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

 

[SkipVought]

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

http://www.TheOfficeExperts.com

 

[foundryqa]

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?

 

[SkipVought]

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

http://www.TheOfficeExperts.com

 

[foundryqa]

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

 

[SkipVought]

I don't understand what all the calculations are about

=VALUE(C1)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[foundryqa]

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