change a number looking vs date

[ukrockhit]

hi I have been working on a database that keeps account of numbers etc,

at the moment I have a section that says it will bring a price depending on how many weeks past the end date it is:

if the end date was 16.5.10 and todays date was 1 week past (23.5.10)then I type a 1,
if 2 weeks past then I type a 2 (30.5.10) is it possible to create an statement that would look at the date and when it gets to 7 days,14 days ,21 it would show the number 1, 2 ,3

I have been trying but have got a bit stuck, even if u canjust tell me the start of the statement I need :if, vlookup etc

thanks all

[teylyn]

Hello, with your date to be examined in column A, you could use

=INT((today()-A2)/7)

copy down.

cheers

[ukrockhit]

I added the examined date to the correct place but just keeps coming up #value, hmm

[teylyn]

time to post a workbook, maybe? So we can see where/what your data really is.

[ukrockhit]

here is a version of the test doc I am trying out

[teylyn]

Your "dates" are not dates, but text. Enter the dates as dates, format them with a date format to show how you would like to see them. Then use the formula I provided earlier.

[Roshan10043]

The date you are storing is in a text format. Store it in a date format Teylyn's formula will work fine.

[ukrockhit]

does it matter what date formate, if I point the formula u gave to the end date it pops up value,

sorry to be hastle I really appreiciate your help

[teylyn]

Pardon?

Yes it does matter. It's not about format of the cell. It's about the data type. You've entered text. Excel does not recognise that as a date and can not perform any calculations with it.

If you enter real dates, Excel can.

[ukrockhit]

sorry I mistyped that question I meant when u format the celll to date there are different layouts to show the date

[munkee]

pick the format you want it will not affect anything.

excel stores dates as a number like 40686, when you select a date format it just dresses this up to look like a "real" date.

choose how you want it to be shown.


If you are getting a #value error it is because your date is stored as "text". You can check this by changing the format of the date to "number" if it stays the same and doesnt convert to something like 40584 style then it is still text.

To convert the number you need to use =date() function. Split the "text" date in to month, day, year using left(), right() and mid() functions. Then insert those in to date()

[ukrockhit]

Oh great got it going, Thank you soooo much guys you have been awesome, I will look at others and help if I can, not just here to take from you,

brill

thanks for this