Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

This is sort of related to a previous question. My formula, which seemed to work fine yesterday now gives strange results.

Today is the 30th of March (30/03/10). It's 10:11am on the clock that the computer is using for the time stamp in the NOW() part of my worksheet. Below is the formula and a screen shot of the results/columns.

Why does it show 1/2 day, and also where does 23 1/2 come from?

The NOW() is in a hidden column (F2)...which I forgot to unhide before I took the screen shot.

=IF(ISBLANK(I2),ROUND(MOD(H2-F2,24),2),ROUND(MOD(I2-F2,24),2))

Screen shot of the columns in question.

share|improve this question
    
You don't need all that MOD stuff, since both NOW and the date in H or I are dates, the difference will always be in days or parts thereof. ROUND it if you want to (but I would not bother if you are going to format the cells as fractions of fixed maximum length). =IF(I2="",H2-$F$2,I2-$F$2) seems a bit simpler to me. This would still give you 1/2 (as per Sux2Lose's answer), but the 23.5 would be replaced with -1/2 which gives a better indication that this is overdue. If you want to use target completion date to mean "by end of the working day on that day", then add eg 0.75 into your formula –  AdamV Apr 9 '10 at 11:57
add comment

1 Answer

up vote 1 down vote accepted

If you didn't explicitly enter in the time in column I it defaults to midnight. So the 23.5 row is getting a negative number (30/3/2010 12:00AM - 30/3/2010 10:11AM).

As for the 1/2, there is approximately a half day between 30/3/2010 10:11AM and 31/3/2010 12:00AM.

share|improve this answer
    
So I should just enter a time manually, and not use the NOW function? I did like the aspect that as my colleagues opened the the document next day the days remaining moved. But, if it moves in strange ways then there is no point. –  RocketGoal Mar 31 '10 at 8:19
    
You can still use the now function. Try =int(now()) to strip out the time portion. –  Sux2Lose Mar 31 '10 at 16:51
2  
if you are going to use INT(NOW()) you might as well use TODAY(). and don't put it in every cell of column F, since it is volatile, put it in a single cell and refer to it from there. –  AdamV Apr 9 '10 at 11:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.