converting dates

Hi All

I have a couple of excel problems to do with dates (Excel XP/2002).

I have a formula that displays one date minus another and displays the answer as the number of days. Firstly, is it possible to convert the data to display as year and days (375 displays as 1 year and 10 days) as it displays as a decimal point when I divide by 365.

Secondly does anyone know a formula that sorts out leap years (see above).

Any help, suggestions or tutorial links greatly appreciated

Rexmann
0
rexmann (52)
8/3/2004 11:33:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
840 Views

Similar Articles

[PageSpeed] 2

As long as you assume 1 year is always 365 days

=INT(A1/365)&" year(s) "&MOD(A1,365)&" days"

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rexmann" <rexmann@discussions.microsoft.com> wrote in message
news:C88AFEAD-000E-40A6-A512-45294665D2E1@microsoft.com...
> Hi All
>
> I have a couple of excel problems to do with dates (Excel XP/2002).
>
> I have a formula that displays one date minus another and displays the
answer as the number of days. Firstly, is it possible to convert the data to
display as year and days (375 displays as 1 year and 10 days) as it displays
as a decimal point when I divide by 365.
>
> Secondly does anyone know a formula that sorts out leap years (see above).
>
> Any help, suggestions or tutorial links greatly appreciated
>
> Rexmann


0
bob.phillips1 (6510)
8/3/2004 11:45:31 AM
Hi Rexmann!

For a return of years and days:

=DATEDIF(A1,B1,"y") & " y " & 
B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) & " d"
returns 55 y 344 d

(Note: DATEDIF approach using "yd" produces errors)

You'll find that this handles Leap Years OK.



-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au


0
njharker (1646)
8/3/2004 12:48:59 PM
Thank you both for the formulas - just what I wanted

cheers Rexmann

"Norman Harker" wrote:

> Hi Rexmann!
> 
> For a return of years and days:
> 
> =DATEDIF(A1,B1,"y") & " y " & 
> B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) & " d"
> returns 55 y 344 d
> 
> (Note: DATEDIF approach using "yd" produces errors)
> 
> You'll find that this handles Leap Years OK.
> 
> 
> 
> -- 
> Regards
> Norman Harker MVP (Excel)
> Sydney, Australia
> njharker@optusnet.com.au
> 
> 
> 
0
rexmann (52)
8/3/2004 1:37:44 PM
Hi Rexmann!

Always pleased to help. For further details on this topic, you'll find 
Google Searching on "Age" will bring up a whole range of formulas for 
different requirements. But watch out! Some of the age stuff in Excel 
is very buggy and / or uses algorithms that are not thought 
appropriate by many of us mere mortals.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au 


0
njharker (1646)
8/3/2004 2:34:31 PM
Reply:

Similar Artilces:

Is Word Converter Compatible with Server 2008??????
We have tried to install the word converter (so 2003 word can read and save 2008 documents). When you try to open a 2007 document, we get the error " There was an error opening file" We have tried on a production server and also test server had have the same results. Uninstall and reinstall several times and . Run as Administrator, everything but no luck. Everything is up-to-date with the lates service packs. We would install Office 2007, but the clients app that does merge to word is not compatible with Word 2007. Is the word converter compatible with Serv...

Add Actual End Date to Resolved Cases view
Hi We would like to add the actual end date to the Case General Tab or possibly to the Resolved Cases and/or My Resolved Cases view. I know that the Actual End Date is available on the Service Activity and Case Resolution but this field is not available for the Case. Any workaround for this? Thanks Mark ...

Problem with xy scatter with dates
I am trying to create an x-y scatter chart where both the x values and the y values are dates. I highlight the 2 adjacent columns of date data and click on Insert>Scatter. Instead of a scatter graph, I get a line chart with 2 lines (the two data columns). The x values on the graph are simply a count of the data points as in a line chart. If the 2 columns of data are not dates, I get the correct scatter chart, but not if the x-values are dates in which case I get a line chart. How can I get a scatter chart with both x-values and y-values as dates? You could select just the Y val...

Make Table Query
I have a Macro and at the end of the Macro I have a Make Table Query that I would like to automatically save as todays date in the current database. Does anybody have an idea on how I can do this? On Wed, 06 Jun 2007 11:40:24 -0700, dswiders@gmail.com wrote: >I have a Macro and at the end of the Macro I have a Make Table Query >that I would like to automatically save as todays date in the current >database. Does anybody have an idea on how I can do this? A date is just a field value. Saving today's date as a Table is almost certainly VERY bad design. Why do an expensive, awk...

lost graph after convert PDF
I have used Words 2007 to convert to PDF for years and have not problem at all. However, in the last few days, all the graphs in the document are lost after convert into PDF. not knowing why. anyone can help? greatly appreciated. sand Hi, Is your graph connected to a spreadsheet in excel? Make sure that your graph is using values from your Word Document(it may fix the problem). On the other hand, you can try copying and pasting the image onto paint and export it to word as an image(this will be a temporary easy fix but it will do the job if you are on a deadline). Hop...

Date Picker
Hello, Is there a DatePicker in Excel 2007 so I can pick a date when I focus on a cell? If not, where can I get one? Thanks, Miguel Look here: http://www.rondebruin.nl/calendar.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "shapper" <mdmoura@gmail.com> wrote in message news:776296c8-83d8-467e-aefb-c31b24446bf7@b15g2000yqd.googlegroups.com... > Hello, > > Is there a DatePicker in Excel 2007 so I can pick a date when I focus > on a cell? > > If not, where can I get one? > > Thanks, > Miguel ...

Dates are not displayed in ComboBox
I have a From ComboBox and a To ComboBox on a Form; cboFrom and cboTo. The Row Source is this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; The SQL is this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; Nothing is showing in either ComboBox; all I see is a whole bunch of blanks. I just did this in another form in another Access database. I can’t figure out why it’s not working for me now. I’m using Access 2007. I’d sincerely appreciate any insight with this. Thanks! Ryan -- Ryan--...

Year to date sales history of inventory
How can this system provide me with cumulative sales numbers of a particular item ie: How many total gym shorts did we sell in 12 months? Detailed Sales Report with a custom date range. In the body of the report look at the top line (Overall) that will give you the answer you seek my child. Now go caterpillar blossom into a butterfly. Rob "KatieH" wrote: > How can this system provide me with cumulative sales numbers of a particular > item ie: How many total gym shorts did we sell in 12 months? Musta got a new batch o glue, huh? (g) KatieH To pick up where Rob started...

Conditional formatting with dates formula problem.
Hello. I appreciate help on this topic. I'm very new to excel's conditional formatting capabilities and I need help on the following: I have a worksheet where I am using columns A and B to be fashioned into a type of "reverse" library checkout card; I want to flag when 120 days have passed since an item has been checked out. All cells are blank with the exception of the formatting applied to cells in column A. For example, Condition 1 on cell A1 has the formula: =IF(ISBLANK(B1),(A1-TODAY())<120) **I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120) My objecti...

Excel 2003 - VBA
Does anyone have and idea how to create the date of the last day of the previous month? Craig Hi Craig =Dateserial(year(date), month(date), 0) HTH. Best wishes Harald "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nbdc.sbc.com... > Does anyone have and idea how to create the date of the last day of the > previous month? > > Craig > > =TODAY()-DAY(TODAY()) -- __________________________________ HTH Bob "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nb...

Access autonumber field changes to date format when Excel imports
When importing Access data into Excel, the autonumber field in Access is chenged to date format instead of a general number format. I could run a macro to change the number format but this only happens on some Vista machines, not all. Any suggestions as to why this is happening? ...

What date was a batch created
I'm in GP v8.0. I need to know what date a user created and/or saved a batch. I've looked all over and there doesn't seem to be any trail when a batch was created/saved short of turning on the User Tracking system. Please get back to me ASAP - sdanna@sfgmortgage.com First, as you probably know, this is not a forum for getting instant answers because it is not monitored officially. Second, SY00500 stores the batch history. It is in the company database. You will need to create a report based on this table. -- Charles Allen, MVP "Sandy D''Anna, GP Project Ma...

conditional format a date in Excel
I am trying to use conditional formatting to show when a date is less than 30 days in the future. When I use "cell value" "is less than" "="Now()+30" all the dates change. I am trying to use conditional formatting to show when a date is les than 30 days in the future. When I use "cell value" "is less than" "="Now()+30 all the dates change. lose the quotes in the condition =Now()+30 Unless you mean between today and 30 days in the future in which cas =now() would be the first condition and =now()+30 would be the second Al...

Excel 2002 Day/Date format
For some reason on one of my computers, Excel won't allow the cell to be formatted to read "Day, Month Date, Year". The option doesn't exist under the Date category of the Format Cells command. Anyone have any ideas? Hi Have you tried using a Custom format? -- Andy. "The Kingfish" <KCKingfish@earthlink.net> wrote in message news:MGjke.4830$X92.1081@newsread2.news.pas.earthlink.net... > For some reason on one of my computers, Excel won't allow the cell to be > formatted to read "Day, Month Date, Year". > > The option doesn&...

Install dates formating using conditional formating?
I have 2 cells (A1 and A2). I would like to have the following take place. 1. If I place a date that is either past or withinthe next 30 days into A2 format the text as red. 2. If I place any date into cell A1, I need A2 to say N/A. I have been able to do #1 using conditional formating but am stuck on #2. Can anyone help? Thanks -- Jerry Eggleston ------------------------------------------------------------------------ Jerry Eggleston's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9898 View this thread: http://www.excelforum.com/showthread.php...

Julian Dates?
Hi - i bet this is simple - but how do i convert NOW() ... or TODAY() into a julian date like 31206 (day 312, year 06) thanks Worksheet and macro solutions posted by Myrna Larson 1999-06-11 http://groups.google.com/groups?oi=djq&ic=1&selm=an_488468851 --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "ElBozo" <kobebus@verizon.net> wrote in message news:FMgih.5223$4Q6.1916@trnddc06... > Hi - i bet this is simple - but how do i conv...

excel dates 03-25-10
how do i change #(1-365) to the corresponding date in excel? It depends on what result you want. If the number is 1, what date do you want? 1/1/2010? With your number in a1, try the following: =date(2010,1,a1) If that doesn't do what you want, post back with what you want to achieve. Regards, Fred "king2385" <king2385@discussions.microsoft.com> wrote in message news:AD3502E1-F28B-4FD5-8637-478EF98E6F66@microsoft.com... > how do i change #(1-365) to the corresponding date in excel? ...

Dates in excel.
I know excel convertes dates into days since 1900, i just wondered i anyone knows exactly how it does it in terms of basic idea of code as need to write a similar thing in a different language without built i capabilities for date handling. Any links or anything would b appriciated. Thanks Rober -- Message posted from http://www.ExcelForum.com Here are some links: http://www.cpearson.com/excel/datetime.htm#AddingDates http://support.microsoft.com/default.aspx?scid=kb;en- us;214094 HTH Jason Atlanta, GA >-----Original Message----- >I know excel convertes dates into days since 190...

Convert .TXT --> .MDB
I have a .txt file in the following form : ------------------------------------------- text1 ------------------------------------------- text2 ------------------------------------------- text3 ------------------------------------------- .. .. .. ------------------------------------------- text n EOF How can convert this file in a .mdb file with one field and with n records (text1,text2,...,text n) ? I need one only field and eatch record has the text between the lines. I dont speak english very well but lines"---------" dividing the different texts (txt1,txt2...) [txt1 differs ...

Converting rows to columns on a large scale
I have a workbook of names and addresses. All information is in column A... John Doe 111 5th st. New York, NY Joe Smith 1st St. E Chicago, IL etc. I want to convert this entire workbook to column form as follows, John Doe 111 5th St New York, NY Joe Smith 1st St. E Chicago, IL Is there a way for me to do this without using "Paste Special" and "Transpose" for each individual entry.....or tediously moving each entry one by one? bootsy ...

convert numbers to text
Using the formular below,what can I do to add a dash to numbers like 25 (exp. twenty-five) while avoiding adding a dash to 5 (five)? '****************' Main Function *'**************** Function SpellNumber(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " ' String representation of amount MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none DecimalPlace = InStr(MyNumber, &qu...

Dates Puzzle
Hi, I have a file from someone with dates appearing to be formatted in the following custom format mm/dd/yy;@ When I copy them, they change to “actual” date according to their serial number. So custom formatted 04/21/87 when copied to another workbook transforms to 04/20/83 (30,426 serial number). I know how to preserve the dates by using = Date (Year(), month(), day()) function. But can someone please explain why this is happening, since in the future it could be easily overlooked when copying and pasting. Thanks for your input! Using 97 on Windows NT -- ______ Regards, G...

Conditional Formatting with Dates
I have two columns: G25 (submission date) & H25 (delivery date - which defaults to 3 workdays after the submission date). The formula I'm using to generate H25's value is: =IF(G25>0,(WORKDAY(G25,3,Holidays_08))," ") I want to conditionally format H25 to appear RED if the user manually enters a date that is less than 3 workdays. How can I do this? I have tried setting it myself but just can't get it to work. Thanks for your help -- JoAnn JoAnn, You cannot use WORKDAY in a CF - since it is a Analysis ToolPak function. You could use a hidden column th...

space before date in csv files
I often have to import CSV files into excel and the files contain dates such as 1/2/05 12:22:12 PM The problem is that when I import them, there is a space before the date so it is not taking as a date unless I remove the space. I tried to use the "TRIM" command and it does take the space off, but it still won't recognize those values as dates for some reason. If I use the trim function and then copy the values only into another cell it still won't act as a date until I go in to edit the cell and then click out of it. Has anyone ever run into this and do you have any su...

Calculating Dates by Month
I'm pretty new to this game, so here goes..... If I enter a date in cell A1, say 24/11/03.... I then want B1 to tel me what month that is (i.e November-03) But I also want excel to automatically complete cells C1-M1 with th next 11 months (December-03 to October-04). Thanks in advance!!!! : -- Message posted from http://www.ExcelForum.com Hi GreenMan! First your format problem Select A1 Format > Cells > Custom Format type mmmm-yy in the text box. Now copying across: Use the following in B1: =DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+2,0)))) Watch...