Calculating dates

A quick question before i'm off to bed :)
I got several dates, all in the future. I'll just give an example, cause 
it'll be hell to explain otherwise :p
I have a date (Belgian format) 26-07-2008 (= July 26th, 2008). Now I need to 
know how many days have past from 26-07-2004 untill now. So, day and month 
stay the same and year needs to change to the year before the current one. 
Then count the days untill the date now.
I need this to calculate accrued intrest, should someone wonder (or if that 
makes it easier to understand ;) )
Preferably, a short function (if possible) since it needs to be integrated 
into a larger function within Excel 2003.
Thanks in advance and good night :) 


0
kwakkel (12)
3/17/2005 10:50:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
379 Views

Similar Articles

[PageSpeed] 3

Assuming your date is in A1

=A1-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))

if now is 26th July 2008

or

=TODAY()-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))

or if now is today

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Kwakkel" <kwakkel@skynet.be> wrote in message
news:423a099e$0$10333$ba620e4c@news.skynet.be...
> A quick question before i'm off to bed :)
> I got several dates, all in the future. I'll just give an example, cause
> it'll be hell to explain otherwise :p
> I have a date (Belgian format) 26-07-2008 (= July 26th, 2008). Now I need
to
> know how many days have past from 26-07-2004 untill now. So, day and month
> stay the same and year needs to change to the year before the current one.
> Then count the days untill the date now.
> I need this to calculate accrued intrest, should someone wonder (or if
that
> makes it easier to understand ;) )
> Preferably, a short function (if possible) since it needs to be integrated
> into a larger function within Excel 2003.
> Thanks in advance and good night :)
>
>


0
bob.phillips1 (6510)
3/17/2005 11:10:26 PM
That one works, but I'm afraid I made a mistake. My apologies.
This formula is correct for all dates with DAY-MONTH after today. It isn't 
however for DAY-MONTH before today.
So, if my date is 24-06-2010, the formula returns 267, which is correct.
If my date however is 23-02-2009, it returns 389. While it should return 23.
Is there another easy way to do that? ... Or does this complicate things 
much?
In any case, thanks for your help!

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> schreef in bericht 
news:uHInCa0KFHA.4032@TK2MSFTNGP14.phx.gbl...
> Assuming your date is in A1
>
> =A1-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))
>
> if now is 26th July 2008
>
> or
>
> =TODAY()-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))
>
> or if now is today
>
> -- 
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Kwakkel" <kwakkel@skynet.be> wrote in message
> news:423a099e$0$10333$ba620e4c@news.skynet.be...
>> A quick question before i'm off to bed :)
>> I got several dates, all in the future. I'll just give an example, cause
>> it'll be hell to explain otherwise :p
>> I have a date (Belgian format) 26-07-2008 (= July 26th, 2008). Now I need
> to
>> know how many days have past from 26-07-2004 untill now. So, day and 
>> month
>> stay the same and year needs to change to the year before the current 
>> one.
>> Then count the days untill the date now.
>> I need this to calculate accrued intrest, should someone wonder (or if
> that
>> makes it easier to understand ;) )
>> Preferably, a short function (if possible) since it needs to be 
>> integrated
>> into a larger function within Excel 2003.
>> Thanks in advance and good night :)
>>
>>
>
> 


0
kwakkel (12)
3/18/2005 7:16:14 AM
Would you care to explain why it should be 23, as for the life of me I don't
see it by the rules you have posted so far. That date gives a result of

TODAY()-23/02/2004, or 389, which is exactly as requested. 23 only happens
if the date uses this year, but you specifically state '... and year needs
to change to the year before the current one ...'.

Perhaps, and this is guessing, you mean

=TODAY()-(IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY(),DATE(YEAR(TODAY()
),MONTH(A1),DAY(A1)),DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))))


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Kwakkel" <kwakkel@skynet.be> wrote in message
news:423a8044$0$14971$ba620e4c@news.skynet.be...
> That one works, but I'm afraid I made a mistake. My apologies.
> This formula is correct for all dates with DAY-MONTH after today. It isn't
> however for DAY-MONTH before today.
> So, if my date is 24-06-2010, the formula returns 267, which is correct.
> If my date however is 23-02-2009, it returns 389. While it should return
23.
> Is there another easy way to do that? ... Or does this complicate things
> much?
> In any case, thanks for your help!
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schreef in bericht
> news:uHInCa0KFHA.4032@TK2MSFTNGP14.phx.gbl...
> > Assuming your date is in A1
> >
> > =A1-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))
> >
> > if now is 26th July 2008
> >
> > or
> >
> > =TODAY()-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))
> >
> > or if now is today
> >
> > -- 
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Kwakkel" <kwakkel@skynet.be> wrote in message
> > news:423a099e$0$10333$ba620e4c@news.skynet.be...
> >> A quick question before i'm off to bed :)
> >> I got several dates, all in the future. I'll just give an example,
cause
> >> it'll be hell to explain otherwise :p
> >> I have a date (Belgian format) 26-07-2008 (= July 26th, 2008). Now I
need
> > to
> >> know how many days have past from 26-07-2004 untill now. So, day and
> >> month
> >> stay the same and year needs to change to the year before the current
> >> one.
> >> Then count the days untill the date now.
> >> I need this to calculate accrued intrest, should someone wonder (or if
> > that
> >> makes it easier to understand ;) )
> >> Preferably, a short function (if possible) since it needs to be
> >> integrated
> >> into a larger function within Excel 2003.
> >> Thanks in advance and good night :)
> >>
> >>
> >
> >
>
>


0
bob.phillips1 (6510)
3/18/2005 9:21:49 AM
I know, i made a mistake in my explanation.
The idea is to calculate the intrest from a bond. The intrest is paid every 
year. So, it's impossible to have unpaid intrest for more than 1 year :) 
It's a bit like a YEAR-TO-DATE, where the YEAR has an extra condition I 
guess.
As for your new function: it's exactly what I need :) Thanks a lot!

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> schreef in bericht 
news:%23KJmrv5KFHA.4056@TK2MSFTNGP14.phx.gbl...
> Would you care to explain why it should be 23, as for the life of me I 
> don't
> see it by the rules you have posted so far. That date gives a result of
>
> TODAY()-23/02/2004, or 389, which is exactly as requested. 23 only happens
> if the date uses this year, but you specifically state '... and year needs
> to change to the year before the current one ...'.
>
> Perhaps, and this is guessing, you mean
>
> =TODAY()-(IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY(),DATE(YEAR(TODAY()
> ),MONTH(A1),DAY(A1)),DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))))
>
>
> -- 
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Kwakkel" <kwakkel@skynet.be> wrote in message
> news:423a8044$0$14971$ba620e4c@news.skynet.be...
>> That one works, but I'm afraid I made a mistake. My apologies.
>> This formula is correct for all dates with DAY-MONTH after today. It 
>> isn't
>> however for DAY-MONTH before today.
>> So, if my date is 24-06-2010, the formula returns 267, which is correct.
>> If my date however is 23-02-2009, it returns 389. While it should return
> 23.
>> Is there another easy way to do that? ... Or does this complicate things
>> much?
>> In any case, thanks for your help!
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schreef in bericht
>> news:uHInCa0KFHA.4032@TK2MSFTNGP14.phx.gbl...
>> > Assuming your date is in A1
>> >
>> > =A1-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))
>> >
>> > if now is 26th July 2008
>> >
>> > or
>> >
>> > =TODAY()-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))
>> >
>> > or if now is today
>> >
>> > -- 
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Kwakkel" <kwakkel@skynet.be> wrote in message
>> > news:423a099e$0$10333$ba620e4c@news.skynet.be...
>> >> A quick question before i'm off to bed :)
>> >> I got several dates, all in the future. I'll just give an example,
> cause
>> >> it'll be hell to explain otherwise :p
>> >> I have a date (Belgian format) 26-07-2008 (= July 26th, 2008). Now I
> need
>> > to
>> >> know how many days have past from 26-07-2004 untill now. So, day and
>> >> month
>> >> stay the same and year needs to change to the year before the current
>> >> one.
>> >> Then count the days untill the date now.
>> >> I need this to calculate accrued intrest, should someone wonder (or if
>> > that
>> >> makes it easier to understand ;) )
>> >> Preferably, a short function (if possible) since it needs to be
>> >> integrated
>> >> into a larger function within Excel 2003.
>> >> Thanks in advance and good night :)
>> >>
>> >>
>> >
>> >
>>
>>
>
> 


0
kwakkel (12)
3/18/2005 10:03:49 AM
Reply:

Similar Artilces:

calculating hours into date format
hi I would like a formula that will allow a user to input a figure (hours and minutes) into a cell that will calculate the end date and time based on a start date and time. For example, Enter 30 hours, static start time is 01/01/04 00:00hrs, the formula would then calculate that the end figure is 02/01/04 06:00hrs. Essentially it is calculating the number of hours & mins added onto the start date with the end result a date and time later than the hours & mins entered Hope this makes sense! Hi in A1 put your date in B1 enter your hours in the format hh:mm C1: =A1+B1 and forma...

Date Navigator
I use Outlook 2002 ... this just started happening & I do not know what to do to resolve. Any assistance would be appreciated. In Date Navigator portion of Calendar, January 4, 2007 is bolded even though I have no appointments on that day? How can I get this date unbolded? Thank you - EJS ...

time calculations #6
Hello! Does anyone know how you can calculate worked hours in a day on the basis of a start and end time? =(end_time-start_time)*24 -- HTH Bob Phillips "Aline" <A_Mangelschots@hotmail.com> wrote in message news:1127724380.330877.175210@o13g2000cwo.googlegroups.com... > Hello! > > Does anyone know how you can calculate worked hours in a day on the > basis of a start and end time? > Hi Aline With start time in column A, and end time in column B, enter in C1 =MOD((B1-A1),1) will give the time interval, even allowing for time period where the start is on...

Calculating Tax on 100% Discount (POS V1.3.1006)
Hi everyone. Please check this: On the POS put the Discount (Shift-F3) to 100% for the whole transaction. Enter an item that requests the Price entry at the POS. Enter a price. Watch the Tax at the bottom of the screen. You get a receipt with a Total of 0,00 including a tax of what the regular tax would be if the item wouldn't have a 100% discount. Isn't that a bit weird? Chris try updating to 1.3.1011 and see if its fixed. "Christian Olsen" <ChristianOlsen@discussions.microsoft.com> wrote in message news:14D63EDA-C3B9-46F2-8720-75CA2725D429@microsoft.com... &g...

add a date without holidays
Hi friends, I have a problem, in a macro I need to calculate a date adding working days only. This add not include saturday / sundays and holidays. For example: 21/5 + 6 days = 31/5 How i can to do ? Thanks in advance and sorry by my english, Christian For adding days, use the DateAdd function. It won't return an invalid date like 31/5. The Weekday function returns the number of the day, for instance this Weekday("22.05.2010" ,vbUseSystemDayOfWeek) returns 6 for Saturday, tomorrow it will return 7 for Sunday. For holidays I can't help...

Lookup for oldest date among different dates
Hi all, I badly need your help in the below formula Example: Structure : Account includes Sub-accounts and each sub-account includes dials Formula : Account activation date = Oldest Sub-account activation date where there is more than one account and hence different activation date for each account and in turn the sub-accounts that belong to each account Below the example A B C D Account # Sub-Account # Account Sub-account activation activation ...

Insert date en time with onchange function
Hi, I have a ntext field in a service indenity . When it is edited i want automaticly insert date en time. Can anyone help? Kind regards Maarten Kievit You may review this website on using Javascript to get time. http://www.tizag.com/javascriptT/javascriptdate.php In your onChange event for that field, you may add var currentTime =3D new Date() var month =3D currentTime.getMonth() + 1 var day =3D currentTime.getDate() var year =3D currentTime.getFullYear() crmForm.all.[Your Field].DataValue =3D month + "/" + day + "/" + year; hope this helps. Darren Liu, Microso...

How to create a calendar from date information in XL sheet.
I have an XL spreadsheet that contains multiple columns, one of which contains data in date & time format. Is there a way to export this data and associated information from the record to another application, for example Word, and create a calendar? Any suggestions how to manipulate this XL data are welcomed. Thanks! ...

date is in 20010129 format, how can I get it in m/d/y format
-- jsanders Data>text to column, go to step three and select date and YMD and for future posts it's considered rude just using the subject line Regards, Peo Sjoblom With the value 20010129 in cell A1, enter this formula in B1: =DATEVALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)) and the format the cell as a date. The various functions parse the 20010129 string into its components, and the DATEVALUE converts that string to a datevalue that Excel can recognize. ...

calculating risk
Hi I use a paper form at work that asks 3 multiple choice questions. The answer to each question is given a score, and the total score is then used to calculate whether risk is low, medium, high or very high. What I'd like to do is set this up in Excel 2002 so that the user simply clicks to select their choice for each question, and the score then results in the assessed risk level being printed on screen. Can anyone please either give me an outline of how to do this, or point me to where I can find a sample that I could modify. My email address as shown in newsgroups is fictitious. B...

Current Date 01-20-10
I would like to filter on todays date. What is the field name for the current date. Thanks. Harry Try "Current Date." - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I would like to filter on todays date. What is the field name for the > current date. Thanks. > > Harry > ...

calculating Week numbers in a time table
I have a weekly timetable table for school terms with the following fields WeekID (automatic number), WkbeginDate (date), WkNo ( number 1 or 2). The WkNo can be either 1 or 2 . How can I get the Wkno field to automatically update with the correct week number for each new record ie if a record has week value 2 the next record will have a WkNo value of 1? I would prefer to do this using a query rather than a form. The timetable is not for the whole year and has 'holiday' breaks, sometimes restarting on a week 2, sometimes on a week 1. Hope this makes sense! Hilarys =?Utf-8?B?SGls...

counting dates #2
in cells D & E i have a series of dates. I want to put a formular E - D = calculated number of days and i also dont want it calculating weekend? the purpose of this is excel to work out how many days it takes to serve a document is this possible and if so what is the formular please =NETWORKDAYS(Start_date,End_date,holidays) Holidays would be a range containing the dates of public holidays you wish to de excluded from your calculation. for example in your case, if you entered the range of holiday dates in cells H1:H10 =NETWORKDAYS(D1,E1,H1:H10) -- Regards Roger Govier "jenn...

calculating time (for payroll)
I'm trying to use Excel like a "time clock" to check in/out of work. I want to be able to write the time in a certain cell that I came into work, left for lunch, came back from lunch, and left for the day. I then want to add up the hours for the week and any hours up to 45 I need to multiply by a dollar amount. Also, any hours above 45 I need to calculate for overtime. I can add the ours to get a total for the week but am having a hard time with multiplying "Time" by a dollar ammount. (Nevermind that I have to find the amount of hours overtime) Attached is a very s...

Subtracting minutes from a date
Is there a way to subtract 15000 minutes from todays date and show the results in date format. A1 = todays date b1= #minutes c1= A1-b1 (the date that many minutes ago) =A1-(B1/(24*60)) Format as Date -- Kind Regards, Niek Otten Microsoft MVP - Excel "J. T. SYLVESTER SR." <sylvester1501@peoplepc.com> wrote in message news:663e99ad.0410011045.29cd532b@posting.google.com... > Is there a way to subtract 15000 minutes from todays date and show the > results in date format. > A1 = todays date > b1= #minutes > c1= A1-b1 (the date that many minutes ago) Su...

Multiple date validation in a single cell
I am trying to do a rather complicated validation (I think it i anyway!) I want to allow ONLY dates of a particular format to be entered int the one cell - i.e. YYYY OR mm/YYYY OR dd/mm/YYYY For example, the same cell can have either one of these format entered, but MUST match one of them to be accepted. 1970 is OK and is captured in the cell as 1970 (not 1/01/1970) 70 is assumed to mean 1970 on entry 05/1970 is OK, captured in the cell as 05/1970 (not 1/05/1970) 5/70 or May-70 is assumed to mean 05/0970 on entry 05/06/2004 is OK 5/6/04 or 5 June 04 is assumed to mean 05/06/2004 on entr...

Hide Alternate Labels on Date Axis
Hi, I'm having trouble trying to hide every other label along a horizontal date axis. I start on the 01-Jan up to 10-Jan and through using the 'Format Axis'->'Scale'->'Major Unit' and setting this to 2 I can hide the labels: '02-Jan'; '04-Jan'; etc. However, I want to hide the odd dates: '01-Jan'; '03-Jan'; etc. I have tried the various combinations with the dialog box but can't get anything to work. Should I be using a custom formula or something? Any help is much appreciated. Paul. In article <1168177092.892944...

between calculation?
Can anyone help me with a little problem? Im sure you can I have a row of cells with percentages in so looks something like this 189% 140% 130% 120% 116% etc etc. Any way what I want to acheive is in the colum next to this I woul like a formula that would say if the value in the percentage colum wa between 200%-175% then it would be 30p. Or if its between 174.99%-150 then it will be 25p. Working its way down to zero. Can this be done in Excel? As I dont know where to start I have trie a few formulas myself and got a response of complete mash : -- Message posted from http://www...

Another question regarding Dates..
First, thanks to the replies to my previous post. How can I work out the day & month when I only know the Year and Day Number? Ie Year = 2004 day number = 302 The answer I'm looking for is 28/10/2004 Again, taking into consideration the leap years Thanks again in advance =DATE(2004,1,302) Regards, Peo Sjoblom "Anthony Slater" wrote: > First, thanks to the replies to my previous post. > > How can I work out the day & month when I only know the Year and Day Number? > > Ie > > Year = 2004 > day number = 302 > > The answer I'...

Date an time on WinXP.
Does anyone know where windows Xp gets the time and date it displays from? My system date and time often fails, resulting in the time beiing 5-5mn delayed compared to what it shoul have been . I wonder what can cause that???? ...

how do I stop Excel taking decimal fractions as dates?
sometimes when I enter decimal fractions Excel thinks this is a date. How do I stop this? Hi Insert a single quote before the fraction '3/4 The quote will not show up in the cell, but it will tell Excel to treat the cell as Text. Alternatively, Format>Cells>Number>Fractions 3/4 will display as 3/4 but will be stored as 0.75 and can be used in further calculations. -- Regards Roger Govier "aa" <A@aa.com> wrote in message news:efV2MhV9GHA.4376@TK2MSFTNGP03.phx.gbl... > sometimes when I enter decimal fractions Excel thinks this is a date. > How do ...

i need monthly ledger that is catagorized by payment due dates
"j.marie" <j.marie@discussions.microsoft.com> wrote in message news:9C223E6B-0C7C-46AA-BEAE-2B49F97D4949@microsoft.com... > Nothing. PLEASE write your question in the body of the post, not in the subject line. This is not a chatroom. Thankyou. In answer to your question, you would be better advised to buy an inexpensive dedicated accounting package. That will do what you want "out of the box". ...

Remove time portion from date on Excel 2007 chart
I am trying to remove the auto generated time of 00:00 to every date in my chart. I have tried setting the format, but it just ignores whatever I try to input. Any help would be appreciated. Where does this appear? Which labels? - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ NeoFax wrote: > I am trying to remove the auto generated time of 00:00 to every date > in my chart. I have tried setting the format, but it just ignores > whatever I try to input. Any help would be appreciated. ...

date time problem
I would like to have a date cell that can have an input mask of __/__/__ so that a user need only enter the numerics of a given date; i.e 052405. I I tried setting the cell format to custom mm/dd/yy and I get a return date of 10/1/43 !!!!!???? Why has excel lost the correct date? Regions global show the correct year range, and Excel is set to 1900 time? What do I need to do to correct this? Thanks XL's parse engine and display engine are separate - the displayed format has nothing to do with how the entry is parsed (other than Text entries are not parsed at all). For a workaro...

Calculating tax on rebates
Hello- I need to get a quick calulation to help me sovle a problem. I need to issue a $50.00 credit, but need to take a tax rate into account. I need to know the calculation if the $50.00 final credit amount is in A1, and the tax rate is in A2, what calculation would be in cell A3 that would show the pretax price? This is pretty straightforward algebra: f = p * (1 + r) So A3: = A1 / (1 + A2) In article <2dd290b7-5b71-470a-9af0-8cd45678f20a@j35g2000yqh.googlegroups.com>, Sabosis <scott.sabo@henryschein.com> wrote: > Hello- > > I need to get a quick calul...