fiscal year

Senilna

New member
Joined
Jan 15, 2015
Messages
1
Reaction score
0
Points
0
Hi all.
I just started new job and im already stock with excel. Hope someone can help me. Is there some way how to calculate days from fiscal year? Im working with date data: 05/01/15 and fiscal date : Q4 wk11 I need create new column that will show that order is created in or not in 28 days. This example i used will show not because there is only 1 week between date and fiscal year date. Thanks for help.
 
Hi
Not really understanding your full question, but all dates in Excel are stored as serial numbers, being the number of days since 01 Jan 1900. For example, today, 16 Jan 2015 is day number 4020.
So regardless how you may have your cell formatted to show either 16 Jan 2015, or 01/16/2015 or any other format, Excel stores it as a number.

Therefore it is really easy to calculate the number of days between 2 dates, you just take the EARLIEST date away from the LATEST date.
I emphasis the order, so that you end up with a positive number of days as opposed to negative.
So with your Fiscal date in A1 of say 01 Jan 2015, and your oder date in B1 of 05 Jan 2015, then
=B1-A1 will give you 4
These are the elapsed days between the 2 dates, and it is not counting the 05 Jan as a day, because as far as Excel is concerned, the date is the beginning of the day. If you want the period from 1 minute past midnight on 01 Jan 2015, until midnight on 05 Jan 2015, then you place a +1 at the nd of your formula.

If this doesn't answer your query, then post back.
 
Therefore it is really easy to calculate the number of days between 2 dates, you just take the EARLIEST date away from the LATEST date.
I emphasis the order, so that you end up with a positive number of days as opposed to negative.

Or you just absolute it so as not to care

=ABS(date1-date2)

:)
 
I suspect that the OP is using a 4/4/5 fiscal reporting calendar where
Reporting Fiscal Year=2015
Reporting Start Month= April
Reporting Quarter= 4
Reporting Period=11
Reporting Day=1
With the help of the 9MB DataTableFisc.xlsx by Colin,
this translates to 1 Feb 2015.
The difference between the 2 dates is indeed 28 days.
PowerPivot with Custom Calendar is a sine qua non.
 
Back
Top