Date calculation and rounding within a fiscal year

[kterkuile]

First of all: best wishes to all the geniussus (i.e. you, yes you know who you are). I was wondering if any of you has had this challenge before.

I'm trying to do a date calculation in a fiscal year.

1. I have a date (1-26-2010) and want to add a number of days (90)
2. I then want to round down to the first of that month (so 4-26-2010 becomes 4-1-2010)
3. All these calculations are to be done within a fiscal year (12 months of 30 days each, so 360 days in a year)

I can't get my head round on how to tackle this. 1. I can do 2. maybe but with 3 put together it wholly illudes me. Is there anyone who could help me please? Thanks so much

 

[Tinus Bergakker]

The biggest problem here would be to do a datecalculation that involves a 360 day year so I would suggest

=DATE(YEAR(N2) + INT(L1/360),MONTH(N2) + FLOOR((L1 - INT(L1/360)*360)/30,1),DAY(N2) + MOD(L1,30))

I tested this using

N2 = 1/1/2010 (the starting date)
L1 = 370 (the number of days you want to move forward)

This would not do the truncation. I would use another cell with
DATE( YEAR(NEW DATE), 1, DAY(NEW DATE))

Hope this helps

 

[barry houdini]

Disregarding the fiscal year part if you have a date in A2 and a number of days to add in B2 this formula would add those days and give the 1st of the resulting month

=A2+B2-DAY(A2+B2)+1

....but assuming each month is 30 days (if A2 is 31st it's taken to be 30th) then try this formula using EOMONTH

=EOMONTH(A2,INT((B2+MIN(DAY(A2),30)-1)/30)-1)+1

So if A2 is 2nd June and B2 is 59 the first formula would give you 1st July (because 2nd June + 59 days is 31st July) but the 2nd would give 1st August because July is deemed to have 30 days rather than 31.

 

[kterkuile]

Thanks you guys! That was soooooooooooo quick! I'll definitely try these because they're function based.

I travel by train to my work and back again by train. If all goes well it's a journey of an hour so that leaves me plenty of time to ponder my excel problems of that day and I thought about this one today (I just arrived home after a journey that unexpectedly took more than an hour :grumble).

In the train I realised that i can split the column the date is in (VBA alas), add 3 to the monthnumber (and correct for october, november & december and the following year) unless the datenumber is 1 in which case I should add 2 to the monthnumber, put the date back together with the value of 1 as the datenumber, et voilá - as they say in French, you've got your date and the fiscal year mumbo jumbo turns out to be nothing but a red herring.

Thank you guys for getting me started; you couldn't begin to imagine how grateful I am for both your time and effort! I'll let you know how it turns out.