Simple question: Difference between two dates?

Suppose cell A2 has 6/27/05 - 7/1/05
A3 has 7/4/05 - 7/8/05... and so 
I need to know the formula to coomputer the number of days betwee
those dates, which is obviously 5. The reason it need to keep the rang
in one cell is this will be used in a VLookup function. Thanks

--
dstoc
-----------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2422
View this thread: http://www.excelforum.com/showthread.php?threadid=38132

0
6/22/2005 5:20:13 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
274 Views

Similar Articles

[PageSpeed] 9

Anyway you slice it, you have to get the 2 dates seperated... 

So you can have one column that shows them together for the VLookup '6/27/05 
- 7/1/05' and 2 more for the first and second dates...

OR you can write some tricky VB code to parse out the dates from the string, 
then do the 'date arithmatic' to find your value.

I would choose the multiple column method.. Keeps the logic more straight 
forward and reduces custom code.  If you're concerned about redundancies or 
extra columns, extra columns can be hidden AND remember, excel is NOT a data 
management tool (like a database) it's a tool for analysis.  (ie not really 
designed to 'normalize' all the data.

HTH

"dstock" wrote:

> 
> Suppose cell A2 has 6/27/05 - 7/1/05
> A3 has 7/4/05 - 7/8/05... and so 
> I need to know the formula to coomputer the number of days between
> those dates, which is obviously 5. The reason it need to keep the range
> in one cell is this will be used in a VLookup function. Thanks!
> 
> 
> -- 
> dstock
> ------------------------------------------------------------------------
> dstock's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24225
> View this thread: http://www.excelforum.com/showthread.php?threadid=381328
> 
> 
0
TomHinkle (87)
6/22/2005 6:16:06 PM
I agree with what Tom wrote--put those dates in their own cells.  It'll make
life much simpler.

But if you can't:

=DATEVALUE(MID(A1,SEARCH(" - ",A1)+3,255))
   -DATEVALUE(LEFT(A1,SEARCH(" - ",A1)-1))
(All one cell)

And if your dates are formatted consistently with your windows setting (mdy in
both spots).

dstock wrote:
> 
> Suppose cell A2 has 6/27/05 - 7/1/05
> A3 has 7/4/05 - 7/8/05... and so
> I need to know the formula to coomputer the number of days between
> those dates, which is obviously 5. The reason it need to keep the range
> in one cell is this will be used in a VLookup function. Thanks!
> 
> --
> dstock
> ------------------------------------------------------------------------
> dstock's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24225
> View this thread: http://www.excelforum.com/showthread.php?threadid=381328

-- 

Dave Peterson
0
ec357201 (5290)
6/23/2005 12:40:41 AM
Reply:

Similar Artilces:

asap utilities question
I have Msft. Excel Vsn 2002 and latest vsn. of ASAP Utilities. I used ASAP Utilities to merge single rows of data together so that more than one row will fit on a single line of text. My question now, is, is it possible using ASAP Utilities to separate the data that has been merged back into single rows of data? All information welcomed! Aaron ...

Isinteg Warning Message Question
I ran isinteg -pri -test alltests. I came up with 506 warnings. There are basically 4 types of warnings. They are below. Are these something I need to worry about? What should I do? thanks stewart sschwartz@nal.usda.gov ================================== Warning: MsgFolder 165 (Fid=0001-0000000D1864, Mid=0001- 0000000D559D, Inid=0001-000000F33436): PR_READ_RECEIPT_REQUESTED(0029000B) prop in Messages table and MsgFolder table do not match. Warning: MsgFolder 4 (Fid=0001-0000086DC911, Mid=0001- 00000436F972, Inid=0001-000004A583F1): Error JET_errRecordNotFound seeking to INID for this ...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Steps to Share Outlook on 2 different drives
This is a problem created by a dual boot of XPPSP2 on one drive C:\ and Vista on another drive E:\ that I use to format for new builds of Vista on the same box. I want to take my Outlook 2003 in box and folders on two different drives on one box and combine them so that all the emails go to one account and all the folders can be shared or used on each drive. I want to do the same with Outlook Express as well, and since this is an *Outlook newsgroup, I'll just take what I learn here and apply it to the ..dbx folder(s) in it. The idea is to receive email on both drives in one in one...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Dotfuscator question
Hello Everybody !!! When i create programm using C# i must understand that my programm from exe - module can be converted to the source file by Reflector. In Order do not allow do this i must use dotfuscator, but i did not find any free normal dotfuscator. May be someone tell me what can i do cause now as think we have interesting paradox. We 've got free technology but if we want to protect our products we must buy expensive commercial dotfuscators. Alex Dmitriev You should post this message in a newsgroup which discusses managed code. This newsgroup does not have much managed traff...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

question on the rules wizard
When clikcing on the Rules Wizard, Outlook locks up - consistently. Has anyone got a remedy for this? Thank you, rich rpage@concerto.com ...

OE questions
Ok, I have two questions: 1. With Thunderbird I can use this with a hotkey manager to compose a new message with Tbird "D:\Program Files\Mozilla Thunderbird \thunderbird.exe -compose" -- how can I do the same for Outlook Express and Outlook 2003? 2. When I go to load Outlook Express I get this error message, followed by the error message below it: http://i180.photobucket.com/albums/x296/gwar_1/Untitled-3.png how can I fix this and use Outlook Express? Thanks! posted to the outlookexpress newsgroup via crosspost --=20 Peter Please Reply to Newsgroup for the benefit of others Re...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

Mailboxsize is different
hi! i've got a crazy problem. when you look at the mailboxes in the exchange system manager, then you see, e.g. user xy with 220 mb mailboxsize. but when you look at the outlook, then the mailbox size is only 150 mb. but there are no filters or something else activated. and the problem is on the local outlook and on the terminalserver outlook. i hope someone has a solution about my problem. thanks. stefan ...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

HQ Licensing question V.2
I have a client running HQ client at 8 stores + HQ server at the warehouse. They would like to be able to use transfers from the warehouse to the stores, so I would have to setup a new PC running Store Ops and a new database, however, in order for the transfers to take place, I need a HQ Client License correct? Do I also need a POS key? I appreciate any insights you may have. Thanks in advance Hi Phil yes - you'll need an HQ Client license so it can exchange with your headquarters - "export" the wh database as you would any store from HQ no - you won't need a PO...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...

MS Project 2007 question
Hello, I am developing a simple schedule with no resources but 2 tasks out of 100 are hazardous and I would like to make the schedule so that when these tasks occur, no other tasks can occur. In other words, even though all tasks are moving around as the schedule is being developed, I would like to make sure no other tasks can be scheduled wherever these 2 hazardous tasks fall. These 2 tasks are serial to every other task in the schedule. Is there a way to do this? Thanks for your help. Mike Create two milestones. Use milestone 1 as the successor task to all tasks th...

Exchange 2003, Two Sites, Two Smarthosts, One Domain, No Internal mail!!
Hi, I hope there is someone out there who can point me in the right direction. We have two sites, Brisbane and Sydney, connected by a frame realy connection. Sydney is currently running exchange 2003 (upgraded by migration from exchange 5.5 about a month ago). Brisbane is currently running their mail through sendmail and sendmail only. We have just installed exchange 2003 on server 2003 box in Brisbane and are trying to get the two sites working in two routing groups. The topology will be the same in both Brisbane and Sydney, in that each city will have an external mail server running sendma...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...