Day Calculation Error

S

Steve

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...
 
D

David Biddulph

Sorry, my crystal ball is a bit cloudy. You may need to tell us what
formula you are using?
 
R

Rick Rothstein \(MVP - VB\)

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
 
P

Peo Sjoblom

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
 
S

Steve

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.
 
R

Rick Rothstein \(MVP - VB\)

=(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
 
P

Peo Sjoblom

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
 
S

Steve

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?
 
R

Rick Rothstein \(MVP - VB\)

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
 
S

Steve

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...
 
Top