php date to excel

[KryptoS]

Hello,

I've a date like this (2007-10-22). Now I need to export it to my excel sheet. I can't export the date directly to my excel sheet because he's exporting it like a string (or something, the format is going wrong).

But I did a test, if I convert the date to int and export it to excel and setup the format of the cel in excel it's working fine.

But how can I convert my date to an int? I did it with mktime, but mktime is counting with seconds. And in excel it's counting with days... So I devided mktime by 86400 sec and I got the date.
But mktime is counting from 1 january 1970, and excel is counting from 1 january 1900, so I'm missing +/-70 years :s.

How can I solve this?

The One And Only KryptoS

 

[Foamcow]

Subtract 70 years?

I'm not sure I fully understand the question.

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[KryptoS]

I have the mktime from php. But that's in seconds. So I have to devide it by 86400 to get the amount of days.

So far no problem. The amount of days for php is counted from 1 january 1970. But the problem is that excel counts the amount of days from 1 january 1900.

Let's say my date is 10 january 1970. So I get from php a value 10. If I export it to excel the date will be 10 january 1900.

So I could ADD 70 years to my php. But how can I do that? I can't do 70y * 365days. Cause sometimes you have 366 days in a year...

The One And Only KryptoS

 

[Foamcow]

Why can't you just output a string in the format that Excel uses for dates?

http://www.cpearson.com/excel/datetime.htm

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[KryptoS]

I'm using the pear 'excel writer'. So if I'm setting my date to 22/02/2007 (excel format I thhink) and output it to excel it's a string.

Strange thing is. That when I push f2 (like you want to add or edit a value) in a cell with the string, do nothing, just press enter to comfirm it changes the string to a date.




The One And Only KryptoS

 

[Foamcow]

No that's not the correct format.
Look at the link I supplied.

Excel works with dates in the format ddddd.ttttt
Where ddddd is an integer of the number of days from 1 Jan 1900 and ttttt is a decimal representing the portion of the day that has passed.

If you output in this format, and the Excel worksheet is expecting this to be a Date type it *should* work out correctly.

I say *should* because I am unable to test this theory in Excel.




--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[HarleyQuinn]

It most certainly does work

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[KryptoS]

ok ok, I understand.

But that's what I'm doing. I send the number of days from php to excel. I know that 'll work. But my problem is. How do I get the number of days from a date in php.

I can get them by mktime (mktime is in seconds so I devide it by 86400). But mktime is counting from 1 january 1970. And like you say, excel is counting from 1 january 1900.

So if I have a date in php 10 january 1970, I get with mktime/86400 = 10. I export (the 10) to excel and I get as date 10 january 1900...

Maybe I just have to count one time manual how many days there are between 1 january 1900 and 1 january 1970. And just add them.

The One And Only KryptoS

 

[HarleyQuinn]

I'm going to say it's 25567 days.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[KryptoS]

Doh!! Just a function in excel to count that. But for me it's 25569 (1 jan 1970).

Anyway, thanks for the help. It's working now!

The One And Only KryptoS

 

[Foamcow]

Maybe I just have to count one time manual how many days there are between 1 january 1900 and 1 january 1970. And just add them.

That would seem sensible lol

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[jpadie]

you can use a julian function to do this.

function getDays1900($date){
 $stop = unixtojd(strtotime($date));
 $start = gregoriantojd(1, 1, 1900);
 return ($stop - $start);
}

 

[KryptoS]

very nice. But I have to do +2 to get the right date.

I don't know why, and I don't care... it works fine

The One And Only KryptoS

 

[jpadie]

i'm surprised Kryptos, that you have to adjust by a number of days. are you sure that you're right? many people get the rules for leap years wrong, and this could well cause the inaccuracy.

Excel has a known bug (at least in some versions) that wrongly calculates leap years. i am not aware that the julian functions in php suffer from the same fault.

 

[KryptoS]

I don't know. I just have a date/string like 2007-11-05. (5 november 2007). If I use your code:

$bDate = unixtojd(strtotime('2007-11-05')) - gregoriantojd(1, 1, 1900);

this give me the value 39389. Input that in excel and you get 2007-11-03.

I think it has to do with the substraction, and that you need to count the first and the last day back in... That's the only reason I can imagine, but for the moment I don't feel like proving it with mathematic prove. So I just accept it and add +2

I'm using office 2003.

The One And Only KryptoS

 

[KryptoS]

Is there an option to 'activate' the unixtojd?

My webserver doesn't know this command.

Fatal error: Call to undefined function unixtojd()...

The One And Only KryptoS

 

[jpadie]

you need to have '--enable-calendar' as a compile option for php.

http://fr.php.net/manual/en/ref.calendar.php