Excel tricks

Written by pmd - - no comments

1- Generate a calendar in Excel

Here is how to generate a calendar in Excel. Using Excel 2010 in my case.

In cell B1 (by example), put a date :

01/06/2018

In cell B2 :

=IF(
MONTH(DATE(YEAR(B$1);MONTH(B$1);CELL("row";B2)-CELL("row";B$1)))=MONTH(B$1);
DATE(YEAR(B$1);MONTH(B$1);CELL("row";B2)-CELL("row";B$1));"")

Then you just have to drag-n-drop down and right (or left).

To highlight saturdays and sundays :

=IF(ISBLANK(B2)=FALSE;OR(IF(WEEKDAY(B2)=7;TRUE;FALSE);IF(WEEKDAY(B2)=1;TRUE;FALSE));FALSE)

Here is the result :<image>

2- Number of days in one month

Put a date in cell B1. Then in another cell, formula is :

=DAY(DATE(YEAR(B1);MONTH(B1)+1;0))

It will return the number of days of the month in cell B1.

How to install Transmission on WNR3500L

Written by pmd - - 1 comment

How to set up Optware on WRT3500L with firmware Tomato Firmware 1.28.0000 MIPSR2-138 K26 USB miniVPN by shibby.
How to install Transmission BitTorrent client on external drive plug on USB of WNR3500L. 

First, you should have a partition in ext2/ext3 in order to be able to install Optware on it (you cannot install optware directly on you router memory neither on FAT and NTFS partitions). 

In administration interface of your router, go to section "USB and NAS >> USB Support". Make sure ext2/ext3 File Systems Support is checked.

I plugged in my router one HDD of 250GB. 249GB formatted in NTSF. 1GB formatted in ext3 called <harddisk label> where I will install different packages such as Transmission.

Procedure:
1. make sure mounted usb harddisk is detected. ls /tmp/mnt/<harddisk> should be present
2. cd /tmp/mnt/<harddisk>
3. mkdir /tmp/mnt/<harddisk>/opt
4. mount -o bind /tmp/mnt/<harddisk>/opt /opt
5. cd /tmp/mnt/<harddisk>/opt
6. install your optware, sh /usr/sbin/optware-install.sh
7. update optware, ipkg update and ipkg upgrade
8. install transmission, ipkg install transmission
9. you can list all packages available with ipkg list

Then, go enable BitTorrent in router adminitration, section "USB and NAS >> BitTorrent Client".

Once it's started you can monitor Transmission via http :

Et voila !

Connect to ftpperso.free.fr from anywhere

Written by pmd - - 1 comment

French ISP provides free website hosting.To enjoy this service, you must be in France to connect to FTP server ftpperso.free.fr.

If you are not in France, find a proxy located in France :
https://hidester.com/fr/listeproxy/

Then in FileZilla, go to Edit >> Settings >> Connection >> FTP >> Generic proxy. Then configure. You might need to test different proxy...

 

Besides that :

  1. create specific ".htaccess" file to activate PHP5.
  2. create folder "sessions" in root folder to be able to use sessions.

 

DDNS on WNR3500L

Written by pmd - - 3 comments

How to set up DDNS on WRT3500L with firmware Tomato Firmware 1.28.0000 MIPSR2-138 K26 USB miniVPN by shibby.

Two services in case one falls :

  • no-ip.com
  • dtdns.com

In block named Dynamic DNS, I choose Use external IP checker because I do not get my Internet connection from my WAN port. 
I set auto-refresh at 1 day even though I'm not sure what it really means.

How to use no-ip :

  1. create free account on no-ip.com.
  2. choose No-IP.com as service in Dynamic DNS 1 options and configure. 

How to use dtdns :

  1. create free account on dtdns.com
  2. read https://www.dtdns.com/dtsite/updatespec
  3. choose Custom URL as service in Dynamic DNS 2 options and configure. 
  4. example : http://www.dtdns.com/api/autodns.cfm?id=username.dtdns.net&pw=password

It works like a charm !

How to double check from command line ? Here :

# wget -q -O - "http://myexternalip.com/raw"
Rss feed of the articles