CODEWITHSUNDEEP
f#
nik
nik

Reputation: 1784

DateTime conversion to Excel Date in f#

I read on stackoverflow that easiest way to convert a DateTime variable back to Excel date was simply to do:

let exceldate = int(DateTime)

Admittedly this was in c# and not f#. This is supposed to work as the decimals represent time and int part represents date. I tried this and f# comes back with the error: 

The type 'DateTime' does not support a conversion to the type 'int'

So how do I convert back to excel date?

More specificly, I m trying to create a vector of month 1st for a period between start date and end date. Both vector output and start date and end date are floats, i.e. excel dates. Here my clumsy first attempt:

let monthlies (std:float) (edd:float)  =
let stddt = System.DateTime.FromOADate std
let edddt = System.DateTime.FromOADate edd
let vecstart = new DateTime(stddt.Year, stddt.Month, 1)
let vecend = new DateTime(edddt.Year, edddt.Month, 1)
let nrmonths = 12 * (edddt.Year-stddt.Year) + edddt.Month - stddt.Month + 1 
let scaler = 1.0 - (float(stddt.Day) - 1.0) / float(DateTime.DaysInMonth(stddt.Year , stddt.Month))
let dtsvec:float[] = Array.zeroCreate nrmonths
dtsvec.[0] <- float(vecstart)
for i=1 to (nrmonths-1)  do
    let temp = System.DateTime.FromOADate dtsvec.[i-1]
    let temp2 =  temp.AddMonths 1
    dtsvec.[i] = float temp2
dtsvec

This doesnt work because of the conversion issue and is rather complicated and imperative.

How do I do the conversion? How can I do this more functionally? Thanks

Upvotes: 1

Views: 405

Answers (1)

JMK
JMK

Reputation: 28069

Once you have the DateTime object, just call ToOADate, like so:

let today = System.DateTime.Now
let excelDate = today.ToOADate()

So your example would end up like so:

let monthlies (std:float) (edd:float)  =
let stddt = System.DateTime.FromOADate std
let edddt = System.DateTime.FromOADate edd
let vecstart = new System.DateTime(stddt.Year, stddt.Month, 1)
let vecend = new System.DateTime(edddt.Year, edddt.Month, 1)
let nrmonths = 12 * (edddt.Year-stddt.Year) + edddt.Month - stddt.Month + 1 
let scaler = 1.0 - (float(stddt.Day) - 1.0) / float(System.DateTime.DaysInMonth(stddt.Year , stddt.Month))
let dtsvec:float[] = Array.zeroCreate nrmonths
dtsvec.[0] <- vecstart.ToOADate()
for i=1 to (nrmonths-1)  do
    let temp = System.DateTime.FromOADate dtsvec.[i-1]
    let temp2 =  temp.AddMonths 1
    dtsvec.[i] = temp2.ToOADate()
dtsvec

In regards to getting rid of the loop, maybe something like this?

type Vector(x: float, y : float) =
   member this.x = x
   member this.y = y

   member this.xDate = System.DateTime.FromOADate(this.x)
   member this.yDate = System.DateTime.FromOADate(this.y)

   member this.differenceDuration = this.yDate - this.xDate
   member this.difference = System.DateTime.Parse(this.differenceDuration.ToString()).ToOADate

type Program() =
    let vector = new Vector(34.0,23.0)
    let difference = vector.difference

Upvotes: 1

Related Questions