convert 360 calendar integers to normal calendar style dates

[dmhg]

Hello,
I am analysing scientific data which uses a 360 day calendar (12 months each with 30 days).
The reference date is 1st December 1950.

The data has a column of integer values, where the integer = number of days since 1950 Dec 1st in the 360 day calendar.

ergo
1 = 2nd Dec 1950
10 = 11th Dec 1950
30 = 1st Jan 1951
60= 1st Feb 1951
89 = 30th Feb 1951 (allowed date in 360 day calendar)

What I would love is a formula that takes my integer value and spits out DD/MM/YYYY in another column
I tried the following, where cell I1 = 1950/12/1 and G3 = my integer days since number

=MOD(( G3 +360*YEAR($I$1)+30*MONTH($I$1)+DAY($I$1)), 30)&"/"&QUOTIENT(MOD((G3 +360*YEAR($I$1)+30*MONTH($I$1)+DAY($I$1)), 360),30)&"/"&QUOTIENT((G3 +360*YEAR($I$1)+30*MONTH($I$1)+DAY($I$1)),360)

However the problem is that I end up with day ranges of 0-29 (instead of 1-30), months 0-11 (instead of 1-12) and the year resets each 1st December (instead of 1st January)

Can anyone help?

Thank you

[MrShorty]

I'd probably suggest starting here to learn how Excel's build in calendar works: http://www.cpearson.com/Excel/datetime.htm#SerialDates

Since Excel's calendar system is based on integers representing days since 1 Jan. 1900, it seems to me like it should be as easy as determining what integer in Excel's system corresponds to 1 Dec. 1950 (enter 12/1/1950 into a cell, then format as general), then add that integer to your integers.

[dmhg]

Dear Mr Shorty,
Thank you for your reply.
What you suggested is exactly what I had done to convert my integer dates from 1950-12-1 which were using the normal (Gregorian) calendar. That method works a treat.

However it doesn't work with the 360 day calendar.

Excel has a 360 day function to count the number of days between two dates using the 360 day calendar: DAYS360. Unfortunately, what I need is the opposite of DAYS360.
For DAYS360
X=DAYS360(start date, end date), where start date and end date are known.
In my case I have my X value and the start date, but I want to solve for the end date.

Is the "source code" for DAYS360 somewhere? Maybe I could reverse it.

Thank you

[MrShorty]

Is the "source code" for DAYS360 somewhere? Maybe I could reverse it.

This is Microsoft we are talking about here. The antithesis of anything that looks like "open source." I strongly doubt you will find the source code for Excel's DAYS360() function.

You might find the source code for the function in some of the open source spreadsheets, which would give you something to work with, since the developers of many of those spreadsheets have tried to make their spreadsheets compatible with Excel.

I'm not familiar with calendaring on this level, so I'm not sure exactly how to do it. It doesn't seem like it should be overly difficult -- you essentially have two "number lines" that overlap each other. You need to figure out exactly how those two number lines overlap. Perhaps it is in not only accounting for the different "starting point", but also accounting for the 5 or 6 days each year that a 360 day calendar ignores.

In an attempt to understand the algebra behind a 360 day calendar, I looked at Wikipedia: http://en.wikipedia.org/wiki/360-day_calendar but did not put enough into it to back out exactly how they overlap a 360 day year onto the Gregorian 365 day year.

For DAYS360
X=DAYS360(start date, end date), where start date and end date are known.
In my case I have my X value and the start date, but I want to solve for the end date.

Off the top of my head, the easiest way I see to accomplish this is to use the built in Goal Seek function. Set up the spreadsheet to calculated X at start date and an estimated end date. Then tell Goal Seek to set X to the desired value by changing end date.

[MrShorty]

This page explains a little about how to use a 360 day calendar: http://www.codeproject.com/Articles/...a-day-calendar

[shg]

I don't know that you can convert an integer value to a date, because DAYS360 changes the end date based on the starting date -- it's inherently an interval measure.