Perl and dates 1

[bluegroper]

I have a small perl project that reads some data from an excel file. One of the strings needed is a date.
Excel gives perl the date as 39261, ie days since 1/1/1900.
Is there an easy way for perl to change 39261 into 6/30/2007 or 30/6/2007?
Sure there's plenty of perl "date" modules, but they're so complicated !
TIA's

 

[prex1]

Assuming the epoch in your system is, as customary, 01/01/1970, do the following:

print scalar gmtime(($ExcelDate-25569)*86400);

This will display something like [tt]Fri Jul 27 09:39:11 2007[/tt], but you can rearrange this by using the list output of [tt]gmtime()[/tt] (or [tt]localtime[/tt]) in list form

prex1

http://www.xcalcs.com

: Online tools for structural design

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[bluegroper]

print scalar gmtime(($ExcelDate-25569)*86400);

Perfect. Fantastic, many thx's. I give you a star.
How or where could or should I have found this for myself ?
There's some reading I've missed out on, or it is simply lack of experience ?

 

[KevinADC]

It is simply a lack of knowledge about how computers calculate what the date is based on the epoch.

time

Returns the number of non-leap seconds since whatever time the system considers to be the epoch (that's 00:00:00, January 1, 1904 for MacOS, and 00:00:00 UTC, January 1, 1970 for most other systems). Suitable for feeding to gmtime() and localtime() .

You could have maybe figured it out had you known that.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[MillerH]

For formatting, check out POSIX strftime. Just do a man strftime to see the different formatting options.

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]POSIX[/green] [red]qw([/red][purple]strftime[/purple][red])[/red][red];[/red]

[black][b]use[/b][/black] [green]strict[/green][red];[/red]

[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$ExcelDate[/blue] = [fuchsia]39263[/fuchsia][red];[/red] [gray][i]# 30/06/2007 in Excel format[/i][/gray]

[gray][i]# Note:[/i][/gray]
[gray][i]# - Excel Dates appear to be saved as days since 1900[/i][/gray]
[gray][i]# - Epoch (unix time's pivotal date) is 25569 in this format[/i][/gray]
[gray][i]# - 86400 is seconds in a day.[/i][/gray]
[black][b]my[/b][/black] [blue]$epochDate[/blue] = [red]([/red][blue]$ExcelDate[/blue]-[fuchsia]25569[/fuchsia][red])[/red] [blue]*[/blue] [fuchsia]86400[/fuchsia][red];[/red]

[black][b]my[/b][/black] [blue]$formatted[/blue] = strftime [red]"[/red][purple]%d/%m/%Y[/purple][red]"[/red], [url=http://perldoc.perl.org/functions/gmtime.html][black][b]gmtime[/b][/black][/url][red]([/red][blue]$epochDate[/blue][red])[/red][red];[/red]

[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [red]"[/red][purple]European style date is = '[blue]$formatted[/blue]'[purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]

[tt]------------------------------------------------------------
Pragmas (perl 5.8.8) used :
[ul]
[li]strict - Perl pragma to restrict unsafe constructs[/li]
[/ul]
Core (perl 5.8.8) Modules used :
[ul]
[li]POSIX - Perl interface to IEEE Std 1003.1[/li]
[/ul]
[/tt]

- Miller