Day Calculation Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a status spreadsheet that counts how many days from a fixed date to
Now(), in one field the date is 16 April 2007, to Now() it is saying it is 3
days, when I move it to a different fixed date of 19 april it tells me 23
days, it should read 60 on 16 april and 57 on the 19 april. I have checked
the formating, and on some it counts properly.

Has anyone encountered this before if so please tell me how to fix it...
Thank you Steve...
 
Sorry, my crystal ball is a bit cloudy. You may need to tell us what
formula you are using?
 
I have a status spreadsheet that counts how many days from a fixed date to
Now(), in one field the date is 16 April 2007, to Now() it is saying it is
3
days, when I move it to a different fixed date of 19 april it tells me 23
days, it should read 60 on 16 april and 57 on the 19 april. I have checked
the formating, and on some it counts properly.

Has anyone encountered this before if so please tell me how to fix it...
Click to expand...

What formula are you using?

Rick
 
1. If you are comparing days and not hours etc don't use NOW(), use TODAY()

2 format the result as general

If I do that I get 63

=TODAY()-A2

where A2 holds 04/16/07

formatted as general
 
Sorry for not providing all the info, the result of the calcualtion I have as
custom, (dd "Days," hh" Hours"), so it shows how many days and hours have
passed since the fixed date and time.
 
=(NOW()-F32) F32 contains(4/16/2007 7:15:00 AM)

Using your formula, I get 63 days for April 16th and 60 days for April 19th.

Rick
 
Will not work since it will return the day of the month 63 days after Jan 00
1900
use something like this instead

=INT(NOW()-A1)&" Days "&TEXT(MOD(NOW()-A1,1),"hh")&" Hours"


--
Regards,

Peo Sjoblom
 
Actually I have the formating at custom dd "Days," hh" Hours"

If formatted General yes I get 63 days after rounding. Is there anyway to
re-format showing days and hours, but keep the "General" construct?
 
Actually I have the formating at custom dd "Days," hh" Hours"
If formatted General yes I get 63 days after rounding. Is there anyway to
re-format showing days and hours, but keep the "General" construct?
Click to expand...

Maybe use this formula instead of the one you posted and leave the cell's
format as General.

=INT(NOW()-F32)&" Days, "&ROUND((NOW()-F32-INT(NOW()-F32))*24,0)&" Hours"

Rick
 
BRILLIANT, Thank you

Rick Rothstein (MVP - VB) said:
Maybe use this formula instead of the one you posted and leave the cell's
format as General.

=INT(NOW()-F32)&" Days, "&ROUND((NOW()-F32-INT(NOW()-F32))*24,0)&" Hours"

Rick
Click to expand...
 
Back
Top