Date Difference 7

[rtoguchi]

In Excell, I would like to create a formula that shows the difference between two dates. I know how to do this for an Access query, but not in Excell. I would want the put the equation in the "Days until Due" column."

Due Date Today's Date Days until Due
5/20/04 5/19/04 1
5/10/04 5/19/04 -9

Would I also be able to make the "-9" highlight in red as well?

 

[glalsop]

You can just subtract the cells, and change the format of the result cell to Number with 0 decimal places. This will give you the number of days.

-Gary

 

[fuzzernet]

easy

format cells as date,use the defalut
then in in the "days until" column use: =A2-B2 etc. where A2 is the due date and B2 is todays date.

Steve

 

[Molby]

You can use conditional formatting to hightlight any negative values in red.

 

[rtoguchi]

Thank you for the quick responses, but For "Today's date", I am looking for something like "Date()" in Excell, so that it will always be "Due Date" - Today's date(which always changes).

 

[bjzielinski]

Use the forumla =DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))
This is volatile so it changes whenever the spreadsheet is viewed.

Bill Zielinski
bzielinski@co.midland.mi.us
County of Midland, Michigan

 

[xlbo]

=Today()

will show today's date in a cell - without any time - bit easier than:
=DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[bjzielinski]

Yeah that works too.
I like to do things the hard way.

Bill Zielinski
bzielinski@co.midland.mi.us
County of Midland, Michigan

 

[Bowers74]

Hi rtoguchi,

I just wanted to point you to my FAQ dealing with some of Excel's Date functions. It might help you out further, or in the future.

faq68-4037 - What are some of Excel's date functions?

Enjoy!



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[macropod]

BTW,

You don't need conditional formatting to format negative values as red - cell number formatting can do that just as readily and with less effort and overhead.

Cheers

 

[Volk359]

Hi Folks,

Is there a way to do this in Access? To take it a step further can a different value be shown depending on the number returned?

What I like to do is have the value shown as an age group, i.e. 3-5, 6-11, 12-Adult.

Thanks in advance!

 

[glalsop]

Hi Volk359 -

If are working in a query, and want to display "3 - 5", "6 - 11", etc., instead of the number, you could look into the iif() function. Something like:

MyDisplay: IIf([age]<3,"0 - 3",IIf([Age]>=3 And [Age]<=5,"3 - 5",IIf([age]>5 And [age]<12,"6 - 11","Adult")))

Good luck

-Gary

 

[Volk359]

OK, but how do I get the age?

Something like this? =DateDiff("yyyy",[Today],[DOB])

And would all of it fit in the query?

 

[glalsop]

Yeah something like that, but we need to take months and days into account if we want to be accurate. So lets create 2 expressions.

First:

Age: DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))


Then we can do the display renage:

MyDisplay: IIf([age]<3,"0 - 3",IIf([Age]>=3 And [Age]<=5,"3 - 5",IIf([age]>5 And [age]<12,"6 - 11","Adult")))

Good luck

-Gary

 

[Volk359]

Ooookay, your not above my head yet but the water's lappin' around my chin! ;-) Sorry, I'm a bit new to this part of Access so thanks for your help and please bear with me.

I understand the logic but I'm putting it in the wrong place as I'm getting an invalid syntax error. Where should it go?

Thanks,

Keith

 

[glalsop]

Keith,

You should be able to paste these expressions directly into the Access query grid, the same cell where you would typically put a field from a table. For the age calculation, you need to be sure to also put the Age: part. Otherwise the display formula won't know where to get the age from.

If this doesn't help, then please post the syntax error and let me know if Access highlights the expression that it is unhappy with.

Good luck

-Gary

 

[Volk359]

Dang, you learn something new everyday. Worked like a charm, my hat's off to you Gary! Thanks!!

 

[Volk359]

Oops, one last question if you please.

If I wanted to filter one age group can I do that in the criteria? I've tried to set it to Like "3 - 5" or using bits of the formula Like "IIf([Age]>=3 And [Age]<=5" or Like (IIf([Age]>=3 And [Age]<=5) and I get an Enter Parameter Value window for Age and I either get everything or nothing.

Thanks,

Keith

 

[glalsop]

In the criteria cell for the age calculation, you can enter the same types of expressions that you would enter for a regular field, no need to refer to [Age] or retype portions of the formula. Try something like this:

>=3 And <=5



-Gary

 

[Volk359]

Mmmmm.... didn't work. I tried to copy/paste it as you've shown it, include quotes, use "Like" and a couple other methods and I still get the Enter Parameter Value window.