Calculating number of days from todays date

[joesiv]

Hi
Sorry if this has been asked before, cant find the exact answer anywhere.
Im trying to set up a spreadsheet which will calculate the number of days a procedure is done in relation to todays date.
For example if the procedure was on 5th November today I would like the cell to read D+1.
If the procedure is due to be done on 7th November the cell would read D-1.
(and if the procedure was done today it would say D0)

Hope this doesnt sound too complex? Will be very grateful if someone can figure this out for me!
thanks
Joe

[xladept]

Hi Joe,

Here's a function:

Please Login or Register  to view this content.

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

Then you can invoke just like a regular Worksheet Function i.e.Data Range

	A	B
10	30-Nov	D+24

where

Please Login or Register  to view this content.

is the entry in B10

And Welcome to the Forum!

[joesiv]

Hey thanks for the quick reply! Thats genius. It works but I need to adjust it as its calculating the wrong way round eg when i put 7th nov it says D+1 when it should be d-1?
any idea how i can make it the inverse?
thanks

[Obsessed]

I think this gives you what you want, and is a bit simpler:

=CONCATENATE("D"&IF((TODAY()-A2)>0,"+","")&TODAY()-A2)

Put in B2, looks at A2. Modify as needed.

DateDifference.JPG

[joesiv]

THanks that also works but again its the wrong way round and if i put tomorrows date it says D+1 when i want D-1?

[Obsessed]

Um...is your computer date correct? Check out my screenshot...it's exactly what you want.

[xladept]

I just switched the signs

Please Login or Register  to view this content.

[joesiv]

Thanks alot! this forum is amazing...trying to set up a spreadsheet for a bone marrow transplant unit so will probably have more questions soon!

[xladept]

You're welcome, under the circumstance, especially so!

[xladept]

It wasn't right:

Please Login or Register  to view this content.

Should be correct now - let me know

[Obsessed]

Heh, still wish I knew why mine isn't working for OP...xladept, any ideas?

[xladept]

Hi Obsessed,

Your formula works for me

Maybe the OP prefers D-0 to D0

[newdoverman]

Hi Obsessed, your formula works just fine. You don't however need to surround the formula with the CONCATENATE. This will produce the same result.

Please Login or Register  to view this content.

If you really want to shorten it this will also work"

Please Login or Register  to view this content.

Those are much better than this just for the sake of a "-"

Please Login or Register  to view this content.

[joesiv]

Thanks still having problems with both methods.
AS the days are still wrong way round to clarify if I put yesterdays date I want it to say D+1 not D-1 ?
Also do you know how I can make it the case that if i leave the cell blank (no date) that the cell that calulates the day remains blank? as it currently says D+149553 if i leave the date blank?
thanks!

[joesiv]

Im making this alot more complex than it should be! but now I have tried changing the + and - signs round on visual basic editor and now whenever i clinic the cell to write the date i get an error msg saying ambiguous name detected: DoneDate
Is this because ive entered more than one command for this?

Does anyone know how i can start again without starting opening a new workbook as already have the table set up now

[Obsessed]

Try putting something like, 7 days out and see what it says. I find it odd that it works for everyone but you.

[Obsessed]

Hi Obsessed, your formula works just fine. You don't however need to surround the formula with the CONCATENATE. This will produce the same result.

Please Login or Register  to view this content.

Yeah, I know about that, but I prefer the Concatenate wording...it helps explain to newer people what it's doing. In practice I personally have never found a need for it though.

[newdoverman]

I don't know why you are getting the wrong answers unless your computer date is wrong. This is the result from my computer using

Please Login or Register  to view this content.

all the other formulae return the same result except for D-0 being D0.

	A	B	C
2	05/11/2015	D+2
3	06/11/2015	D+1
4	07/11/2015	D-0	today
5	08/11/2015	D-1
6	09/11/2015	D-2
7	10/11/2015	D-3
8	11/11/2015	D-4
9	12/11/2015	D-5
10	13/11/2015	D-6
11	14/11/2015	D-7
12	15/11/2015	D-8
13	16/11/2015	D-9
14	17/11/2015	D-10
15	18/11/2015	D-11
16	19/11/2015	D-12
17
18	="D"&IF(TODAY()>A2,"+"&TODAY()-A2,IF(TODAY()<A2,(A2-TODAY())*-1,"-0"))

[xladept]

Hi Joesiv,

You can only have one function named DoneDate - so delete all but the last version

[joesiv]

Thanks the + /- is the correct way around now.
Does anyone know how I can get it to be blank when I remove a date from the cell (currently when I delete the date the cell which works out the funciton will say D+1439 (or another equally long number)

[Obsessed]

What formula are you currently using? Or are you using xladept's function?

[xladept]

Another version:

Please Login or Register  to view this content.

[joesiv]

Thanks xladept! that one is perfect!
Would you mind sending me another one too?
I need another one similar to above without - and + signs and starting at D1 instead of 0
example for todays date it would read D1 and for tomorrow D2 (no minus numbers needed)

(and would I call this function a different name as I would want to use this one for some patients and the DoneDate for others?)

[xladept]

Try this:

Please Login or Register  to view this content.

[joesiv]

Works a treat! thanks very much!

[xladept]

You're welcome and thanks for the rep!

[joesiv]

Sorry xlad the second formula is wrong (as i gave you the wrong info)
I want it for todays date to read D1 and for yesterdays date to read D2 (if I put a future date i want it to remain blank)
Sorry :S

[xladept]

Please Login or Register  to view this content.

[joesiv]

thanks again!

[xladept]

Welcome again!