Formula to Compute # of Days Between a Date and Today 3

[JohnBates]

Hi Excelers,

I have a column that contains a date in YYYYMMDD format...
20020916 for example.

Need to compute the number of days between that columns value and the current date (Now).

I have not tried the DAYS360 function yet. But it assumes 30 days in each month... so the result would not always be exactly accurate.

Does anyone know of a better function to use to get the # of days between 2 dates?
I have Excel 97.

Thanks, John

 

[schat]

John
With your date in A1, try =TODAY()-A1
HTH
schat

 

[JohnBates]

schat,

Your suggestion got me very close.

It works if I enter 2002/09/16 .... get a difference of 7

But the value coming in from another file doesn't contain slashes... just 20020916

How can I automatically insert the slashes... and still define this col as a Date category?

Sorry, I'm just not really good at Excel.

Thanks for your tip. John

 

[majuda]

John,
in cell A1 type in =NOW()
in cell B1 type in the date that you want to subtract from, say 1/1/2002
in cell C1 type =b1-a1 and you will get the number of days from today till that date. The fraction you get can be converted into hours. If you want just change format to show whole numbers

The date in A1 will automatically change each day.

Enjoy

Majuda

 

[schat]

hi john
i didn't read your original post closely enough.

looking at it again, your problem seems to be that your data isn't in a format that excel can treat as a date, which is why what i suggested falls over.

i think your solution is to use the 'text to columns' function in the data menu to convert your data to a date format. assuming you already have the data in an excel file, select one column at a time, go to data>text to columns at which point you'll get the wizard dialog box. at step one, make sure the 'delimited' option is set. click 'next' until you get to step three and select the 'date' radio button, selecting the YMD format. click on finish and your column should now be in date format, although depending on your regional settings the format may not be exacly what you expect. the formula i suggested should now work though. if you want to retain your yyyy/mm/dd format go to format>cells>number tab and select 'custom' and in the 'type' box enter yyyy/mm/dd.

if you are importing your data as a text file, open the file through excel and the wizard will appear automatically. follow the same steps as above, and when prompted to save as an excel file on saving, choose yes.
hth
schat

 

[iGrant]

If the entries in your cells, such as 20020911, are just numbers, you'll need to chop them up into a date.

Try this formula to convert the number 20020911 in A1 to a date:

=DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,5,2)),VALUE(RIGHT(A1,2)))

Then you should be able to do

=int(now()-A2)

to give the number of days (assuming A2 has the first formula above in it)

 

[JohnBates]

Thanks iGrant,

I had to work on other projects for awhile... but now I'm back to creating the world's best spreadsheet

This is what I've done:
- Changed the A column from General to Number (and 0 decimals) The data will always contain 8 digits representing a date ex: 20020911

=DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,5,2),VALUE(RIGHT(A1,2)))

** This formula gives error "You've entered too many arguments for this function **

I think we are very close! But I'm lost as to the error with the formula.

Thanks for any help. John

 

[xlhelp]

Just missing a bracket. Please don't award a star.

=DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,5,2)),VALUE(RIGHT(A1,2)))

 

[JohnBates]

Thanks xlhelp ! Problem solved. John