Use Datedif but for future dates

I have a formula for identifying years, months, days from a past date to 
now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " & 
DATEDIF(C6,NOW(),"md") & " D"

I'd like a formula that can produce the same format (years, months, days) 
between now and a future date.

Any ideas?

Thanks in advance,

Bart 


0
bartmacl (39)
9/1/2006 7:09:49 PM
excel 39879 articles. 2 followers. Follow

4 Replies
2026 Views

Similar Articles

[PageSpeed] 28

Hi Bart

Try this with the date in A2

=IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"Y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m","-"&DATEDIF(A2,TODAY(),"y")& " y " 
&DATEDIF(A2,TODAY(),"ym")& " m")

-- 
Regards Ron de Bruin
http://www.rondebruin.nl



"bam" <bartmacl@yahoo.com> wrote in message news:%2309qzofzGHA.4392@TK2MSFTNGP04.phx.gbl...
>I have a formula for identifying years, months, days from a past date to now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& 
>" M, " & DATEDIF(C6,NOW(),"md") & " D"
>
> I'd like a formula that can produce the same format (years, months, days) between now and a future date.
>
> Any ideas?
>
> Thanks in advance,
>
> Bart
> 


0
rondebruin (3790)
9/1/2006 7:54:23 PM
Oops, This is the correct one for YMD

=IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m " & DATEDIF(TODAY(),A2,"md") & " 
d","-"&DATEDIF(A2,TODAY(),"y") & " y " &DATEDIF(A2,TODAY(),"ym") & " m "  &DATEDIF(A2,TODAY(),"md") & " d ")

-- 
Regards Ron de Bruin
http://www.rondebruin.nl



"bam" <bartmacl@yahoo.com> wrote in message news:%2309qzofzGHA.4392@TK2MSFTNGP04.phx.gbl...
>I have a formula for identifying years, months, days from a past date to now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& 
>" M, " & DATEDIF(C6,NOW(),"md") & " D"
>
> I'd like a formula that can produce the same format (years, months, days) between now and a future date.
>
> Any ideas?
>
> Thanks in advance,
>
> Bart
> 


0
rondebruin (3790)
9/1/2006 7:56:28 PM
Wow, nice. That does it. Thanks very much
Bart

"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message 
news:eFh03CgzGHA.3568@TK2MSFTNGP03.phx.gbl...
> Oops, This is the correct one for YMD
>
> =IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"y") & " y " & DATEDIF(TODAY(),A2,"ym") 
> & " m " & DATEDIF(TODAY(),A2,"md") & " d","-"&DATEDIF(A2,TODAY(),"y") & " 
> y " &DATEDIF(A2,TODAY(),"ym") & " m "  &DATEDIF(A2,TODAY(),"md") & " d ")
>
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "bam" <bartmacl@yahoo.com> wrote in message 
> news:%2309qzofzGHA.4392@TK2MSFTNGP04.phx.gbl...
>>I have a formula for identifying years, months, days from a past date to 
>>now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " & 
>>DATEDIF(C6,NOW(),"md") & " D"
>>
>> I'd like a formula that can produce the same format (years, months, days) 
>> between now and a future date.
>>
>> Any ideas?
>>
>> Thanks in advance,
>>
>> Bart
>>
>
> 


0
bartmacl (39)
9/1/2006 8:06:10 PM
Swap your references around.

DATEDIF(NOW(),C6,  etc.

Earliest date must be first.


Gord Dibben  MS Excel MVP

On Fri, 1 Sep 2006 12:09:49 -0700, "bam" <bartmacl@yahoo.com> wrote:

>I have a formula for identifying years, months, days from a past date to 
>now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " & 
>DATEDIF(C6,NOW(),"md") & " D"
>
>I'd like a formula that can produce the same format (years, months, days) 
>between now and a future date.
>
>Any ideas?
>
>Thanks in advance,
>
>Bart 
>

0
Gord
9/1/2006 8:13:09 PM
Reply:

Similar Artilces:

Exporting Date Modified Field
I'm trying to export Outlook 2003 Contacts to a .csv file. I want to include the MODIFIED field, which is a data field automatically stamped by Outlook. (It shows up on the FREQUENTLY USED FIELDS tab when displaying ALL FIELDS on a contact.) It doesn't come up as one of the mapped field choices when the wizard asks you to map fields to export. Anyone know how to export this field? If not, anyone know how to filter an export to include only those contacts whose MODIFIED field falls within a certain date range? TIA, Kathy Right now you can use our preview edition of ContactGen...

Using Split Window with CDHtmlDialog
Hello, I'm just curious if anyone has tackled this issue in the past. I have a split window application that is working fine with regular form dialogs. But there are a couple views that I 'would have' liked (although I'm about to give up) to have been HTML based, so I add an IDD_FORMVIEW dialog, then used the MFC Wizard to add a class with base class CDHtmlDialog, instead of CFormView class like my other dialogs. I'm still trying to become familiar with basic C++ concepts here, so excuse my ignorance, but what happens at this point is that the Window does't get filled...

Query to find records after specific date and time today
Our contract requires me to post a job for 10 calendar days and it must remain active until 8:00 am the date of the deadline. After that it must be removed from the list. If I post it today (3/15/10 regardless of the time) the job would have to be available right up through 3/25/10 at 7:59am - then it must be gone/unavailble at 8:00 am. The record has a field entitled "Deadline" where date and time would be entered into the field as "03/25/10 08:00:00 am" However, all records with that deadline for that particular date are removed at midnight (00:00:00) o...

Why does message show date/time in Sent box when draft (not sent)
Hi would be grateful if anybody can shed any light on the following problem. I sent an e-mail with a draft e-mail as an attachment. The draft e-mail has not been sent yet and is saved in Drafts in Outlook. Yet the recipient of the message says that the draft e-mail has got a date and time in the Sent box. Upon checking in Sent Items the message has not got a date and time in the Sent box but when you print the message off a date and time does appear in the Sent box does anybody know the reason for this? ...

Last Contact Date 07-27-07
Hi Everyone, I hope someone can assist me. I would like to create a field on the Contact Entity called “Last Contact Date” (this was a cool field that my previous CRM application had). I have no problem creating the field, but I am looking for the best way to populate the field. Any suggestions would be greatly appreciated. Regards, A.Akin Hi, Where do you get the last contact date data from? populated with Last Modified Date? The easiest way is to do an SQL update. I have done that many times to a datetime field and it's not going to break anything. Just remember this is at you...

using outlook,how can i check my message has been sent?
after you send a message check in sent items folder Sharath "learner" <learner@discussions.microsoft.com> wrote in message news:060AC8D3-B30C-4A0C-8E4E-454D5480F672@microsoft.com... > ...

how to get the row and col count of a TMGrid using sendmessage?
I have a 3rd party application which uses TMGrid, I want to get the row and col count of this TMGrid using SendMessage. The Parent Control is a TTabSheet. Anyone know how to do it? I tried the LVM_ message, but failed. Hi, try to find out what messages this control can handle, e.g. with Spy++. Just give it a try,... Regards Kerem -- ----------------------- Beste Gr�sse / Best regards / Votre bien devoue Kerem G�mr�kc� Microsoft Live Space: http://kerem-g.spaces.live.com/ Latest Open-Source Projects: http://entwicklung.junetz.de ----------------------- "This reply is provided as ...

Formula for Date and Time duration
Morning All, I have to work out the time in which a case has taken to resolve on our system. Now in Excel I have the Case Date (Date opened), Case Time (Time opened), Closure Date, Closure Time. Does anyone have a formula that works out the Number of Days and Time it has taken to resolve a case? This needs to not include weekends and the working hours 08:30 to 18:30. This is way beyond my knowledge but I'm sure it can be done, I'm hoping someone has a formula for this already. -- Adam ----------- Windows 98 + Office Pro 97 Evening Adam! I have Date Opened in A1 Time Opened in ...

Struggling with date formulas
Hi everyone. Using E02 on XP. I am trying to work some formulas and I have the general idea what I need to do. I have a date of birth [DOB] and date of hire [DOH]. I need to calculate Eligibility to Participate by calculating [Age] and [Service] and then: If [Age]>21 AND [Service]>1, name the next occuring 01/01 or 07/01 (with the year). In other words, once you attain age 21 and have at least 1 year of service, you are eligible to enroll on the next occuring January 1 or July 1. The ultimate question is "What is the Date of Eligibility?" If DOB is 05/01/1960 and ...

Date Difference 02-24-10
Good Morning. I am trying to figure out how to calculate the number of days between two dates. I have a table callled events. That table has event date, event type, event outcome. For example a defendant will have an Advisement Date (AA as stored in the table) and say a Preliminary Hearing date (PH). When i enter information I will enter the date, event type and the event outcome will be (PD for Pending, CN for Continued, or CP for completed). For example Case Number 10-12345 will have an AA on 01/05/10 which is Completed and a Preliminary Hearing on 02/05/10 which was C...

importing data using a macro
hi i wish to import 300 fields of data into a spreadsheet from a text file, but since spreadsheets are only 250 fields wide. i was wondering if there was anyway of writing a macro that could import the data vertically, so its all in one column or transpose it during importation. the cut and paste method is possible but i have 200 of these text files to import into this spreasheet and don't think this method would be best. thank you time and any effort that you spend on this matter its been much appreciated Jo I suggest you use Access to do this kind of job. With Access, you ca...

Vlookup in vba
Hi I have the following snippet of code: Sheets("mdata").Select Range("E1").Select ActiveCell.FormulaR1C1 = "RVU" Range("E2").Select ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2)),0,VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2))" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault This fills about 5,000 rows with look up data from the rvu ws, and puts in 0 if #N/A is obtained. HOWEVER, if I was ...

Does anyone use ArcServe 11.5 for Restoring
We have a problem when restoring a the exchange database with Arcserve 11.5. This works the first time 26 hours after the backup. Any idea? http://support.microsoft.com/kb/555375 -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Restoring exchange database" <Restoringexchangedatabase@discussions.microsoft.com> wrote in message news:FD4AC97D-782E-4CCB-9962-95F8B089F2D6@microsoft.com... > We have a problem when restoring a the exchange database with Arcserve > 11.5. > This works the first time 26 hours after the backup. ...

How to I create a Project with Start Date = Unknown
HI, I would like to create a Project and want to add all the TASKS, BUT I dont want to link it to a specifc Start date as this is still unknown, The Start date will be the contract signature date which is unknow. Is there anyway I can create the Project to start on a Unknown date? and then Project still shows you in DAYS, not dates how long the project would take? Somone mention Start date should = T0, (T ZERO) but have no idea what that means -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 For availability check: http://users.online....

Last Contact Date 11-14-07
Has anyone come up with a report that can extrapolate last contacted dates from the CRM.? If so please help with some ideas -- IT Manager ...

Auto Send date stamp not updating
I have a series of e-mails set up to auto-send each morning at 4:30am. They do go out when scheduled, but retain the date they were created on instead of updating to the date they were really sent or received, which could be several days back. This means people aren't seeing them because they don't appear with the most recent emails received (since many people keep the in-box sorted by date received). PS...this worked fine the last time I used it in the Fall of 2003. ...

Spreadsheet (use for test)
I need help (direction) in creating a 50 question test in excel. I would like for the tester to select the answer for each question (multiple choice). At the end of the test, a button that will grade the test (Pass, Fail, Miss est..). The person taking the test can not go back and change answers. The test result to be cumulated (placed) on a hidden spreadsheet by name and score. I would need to change (rearrange) the test once and awhile. Is Excel the right program or would word be better? I would have (run) the file on a floppy, jump drive or shared drive. Please help if you ca...

Enter date in one cell, and the week ending date [Sunday] appears in the "W/E" cell
I have an XL 2003 workbook that I enter the date an event occured in cell C7. I want to make another cell [C2]display the week ending date [Sunday], based ono the date entered in cell C7. How can I accomplish this? Thanks, Tonso =7-WEEKDAY(C7,2)+C7 Daniel > I have an XL 2003 workbook that I enter the date an event occured in > cell C7. I want to make another cell [C2]display the week ending date > [Sunday], based ono the date entered in cell C7. How can I accomplish > this? > > Thanks, > > Tonso hi, =IF(WEEKDAY(C7)=1,C7,C7+(8-WEEKDAY(C7))) -- isabelle On ...

Changing username using the registry.
I'm trying to figure out how to change a users -username- for Exce using the registry. I'm needing to change username's of people withou disturbing them -like anyone wants to know this- Any help will be greatly appreciated -- Message posted from http://www.ExcelForum.com ...

What kind of function should I use?
Hi there, I was wondering what type of function should I use in the xml reports for the receipts. I need a rounding function but I can't find info on which language or standard they use. Please help! DO NOT DELETE THIS QUESTION PLEASE! Thank you Julien ------=_NextPart_0001_207E425D Content-Type: text/plain Content-Transfer-Encoding: 7bit Good morning Julien, Thank you for posting your rounding question. Can you please let me know what you are trying to round? Or is this related to your question about discounts? Todd Berger Microsoft Online Support Engineer Get Secure!...

Show email in date groups
In Office 2007 I am able to group my email by date with date headers. Now I have Windows Live Mail. How do I arrange by date, show in groups. The free WLM lacks some of the features of the retail Outlook program. You can sort by date, but no grouping. -- Dave N. MS-MVP (Mail) Windows 7 Ultimate http://download.live.com/wlmail "Janet" <Janet@discussions.microsoft.com> wrote in message news:1F2A4895-7052-40DE-A414-28D8C102CFD1@microsoft.com... > In Office 2007 I am able to group my email by date with date headers. Now > I > have Windows Live...

How can I set a cookbook using publisher?
I need help setting up a cookbook for my church can anyone help churchlady wrote: > I need help setting up a cookbook for my church can > anyone help ================================ File / Page Setup / Booklet / Landscape / OK. Automatically insert Pages? Yes. For additional pages...go to...Insert / Page.... or type...Ctrl+Shift+N -- John Inzer Specifically what kind of help do you need? Have you decided on what you wanted the end product to look like? The size, how it's going to be assembled, etc. -- JoAnn Paules MVP Microsoft [Publisher] "churchlady" &l...

save a document where fields be used but not altered
I have careated a documents that need to be saved but not as read only. The feilds are needed, such as a form. How can I save this material so the format remains but text box alterations and movement areno longer on the page. There isn't a surefire way of locking the form, but you might want to try this as a workaround. Once the form is complete, group all elements together. Then reduce the viewing size down so the entire pasteboard area is visible on your monitor. Draw a single line from the top of the pasteboard area to the bottom and then a second line from side to side. Move t...

Macro to Check Dates
I have a list of names and dates whereby the names could be listed multiple times. What I would like to define is how many days there are between the dates that a name appears. As an example the name Frank appears with a date of 01/01/2010 and then appears down the list again with a date of 03/01/2010. How many days are there between each date for Frank. Frank could be listed more than twice, multiple times. Also there are probably 20 different names in this list that are listed multiple times with different dates and the list is at least a year old. OK Beep Beep, - assu...

Select which file to use in Integration Manager
I would like to be able to select the file that is to be imported with Integration Manager at the time the integration is run. Is that possible? Or can it only be set up for one file. Thanks for any help. Bob You can use vbscript to prompt you for the file name. You could use IM to use the standard Graphical UI to open file etc but that can get complicated... HS "Bob Harrison" <bobh@tds.net> wrote in message news:1139499714.660048.85750@g43g2000cwa.googlegroups.com... >I would like to be able to select the file that is to be imported with > Integration Manager at th...