Time Formula, Any ideas why I'm getting an error?

[kestefon]

I have a table with time range data in the following format: 1100P-0259A. I've written a formula that finds the midpoint of that time range, so in this example, it would be 1:00 AM.

Please Login or Register  to view this content.

The formula appears to display the correct time within the cell, but when you change the format to "number" you see that it has 1 added to it. So, 1:00 AM should be .04166667, but it is 1.04166667. I'm guessing this is because the time range crosses midnight. Is there a quick fix for this, or do I have to add an IF condition?

[Pete_UK]

You can add this to the beginning, instead of the = :

=MOD(

and add this to the end:

,1)

so that you now have:

=MOD( your_formula ,1)

Hope this helps.

Pete

[kestefon]

Thanks Pete!

Quick question: That worked for the vast majority of my data, except for when the resulting time was midnight. The number value is 1.00 instead 0.00. Any thoughts?

[Pete_UK]

Well, it might be a very small fraction of a second before midnight, but with the rounding that you have you won't be able to see the difference. To see if it is exactly 1, put this formula in a blank cell somewhere:

=cell_with_formula = 1

You will get TRUE if it is. Change the second = to < or > to investigate further - I would suspect you will get TRUE for < and FALSE for > and =.

Hope this helps.

Pete