Make a Task scheduler diary

Hi friends, today we are discussing about some uses of excel. Means we are going to create our simple Task scheduler. Task scheduler help us for track our imp work, like (meeting, dating, receiving family from airport etc. Now you can make your own personal task scheduler by using excel.

I am trying to making it simple. Ok let start.

Open a new Excel workbook. Save the Excel file as a name of “task scheduler diary”, now in A1 cell write “Time”, in A3 cell write “Task” or “To Do” which is perfect for you. Now leave the second row, Put the formula in the cell A3 “=TODAY()+TIMEVALUE("9:00 am")” . Open format cells option for format cell for doing this go to format menu and then select format cells you also can open this option by using the following key combination from your keyboard “Ctrl + 1”. Now format the cell like “HH:MM AM/PM” format, for doing this follow the steps, go to number tab of the format cells dialog box then select time from category list then select “1:30 PM” from the sample list. We use this format because it is a task scheduler and we need to manage it through hourly.

Now some discussion about the formula “=TODAY()+TIMEVALUE("9:00 am")”, “=” is used because before using a function in excel it must be started with “=”. Today() commands return the current date and its time set to the 12:00 AM of the current date. Example if today is April 2, 2008 then when you give the today() command it returns “04/02/2008 00:00 AM”. “+” sign is used for sum/add. Timevalue(“9:00 AM”) returns the value of 9:00 AM in excel format. So actually our motivation is input a today value and then add 9 hour with it and it becomes 9 AM of the morning. You can set your own Task scheduler timing by changing the value of timevalue().

Now go to cell A4 and write the following formula on the cell “=IF(AND(A2<>"",A3="",C2<>""),A2+(1/24),"")” drag the same formula to the A33 excel change all the formula by the cell. Now select A4 to A33 range and format cells in the following format “HH:MM AM/PM”. Now discussion about the formula “=IF(AND(A2<>"",A3="",C2<>""),A2+(1/24),"")” it is a logical formula we used here the “IF” and “AND” function for conditioning. The signs <, >, =, <> are used for Less than, Greater than, Equal to, Not equal respectively. Here our motivation is If cell A2 is not equal to blank and A3 is equal to blank and C2 is not equal to blank then add (1/24) means 1:00 hour with the value of A2. Means if all the conditions are meets then one hour is add with the previous hour. Example If previous hour is 9:00 AM then the current hour is 10:00 AM.

Our Task scheduler is almost complete now it is the time for some conditional formatting with our task scheduler for a cool look. For conditional formatting select the cell C3 then open conditional formatting dialog box from the Format menu. Select “formula is” from the conditional formatting dialog box under condition 1. Then put the following formula in the formula box

“=IF(AND((A3-NOW())>=0,C3<>""),TRUE,FALSE)”, then press the format button for set the format when conditions are meet in the cell C3. Here our first condition is, If the subtraction of the value of cell A3 and current time is greater than or equal to 0 and cell C3 is not blank then the condition is true or false.

Press the add button for add a new condition name is condition 2 same here you select “formula is” from the condition 2 category. Then put the following formula in the formula box

“=IF(AND((A3-NOW())<0,c3<>""),TRUE,FALSE)”, again press the format button for format the cell when meet the following condition. Our second condition is If subtract of the value of A3 and the current time is less than zero and cell C3 is blank then condition are true.

Now() command is given for show the current time and date.

Now save our Task Scheduler We just conditional formatting in the cell C3 but we need to more conditional formatting on the other cell for giving a cool look to our Task Scheduler diary. For doing this select the cell C3 and then select copy format cells by “format painter” then leave a cell below and select C5 then conditional formatting is automatically change in the cell C5 repeat the step to the cell C33 save your work.

Our Task scheduler is ready now you can save and tracking your all imp work by the task scheduler diary.

You can download a Task scheduler diary. For downloading a diary click on the following link

Task scheduler

Please send us your comments and idea for make this blog better.

How links cells of two different Excel files.

Hi friends from today we are start our tips & tricks section.

Our first topic is how links two cell by formula. For our example we first save two different files with two different names.

Let assumed name of the first file is “first.xls” and the second file name is “second.xls”.

Now first discuss about the case.

Suppose in first.xls file we have stored some detail of our customer which fields are “Name” in the column A, “Address” in the column B, “Credit price” in column C.

A B C

Name Address Credit price

In second.xls file we have stored some extra data which also include a same filed which is same with the first.xls file. The second.xls file may be look like

A B C

Customer name Product name Price

Now start our programming first we have enter some customer detail in first.xls the two same field are in the second.xls which are Name and Credit price.

We don’t want to enter the same data on both the file.

Now open the both file (first.xls, second.xls), activate second.xls window then select b2 cell then enter the formula “=[first]Sheet1!$A$2”, then drag the formula from b2 to b20(as per your requirement).

Again select C2 cell and type the formula “=[first]Sheet1!$C$2”, drag the formula from c2 to c20 (as per your requirement.

Now save both the work book and close it. Open first.xls and enter some data in it save it and close it. Open the second file what you see the data which is enter in the first cell it is automatically copy in the B and C column of the second.xls file.

Note: - Remember one thing you must be put the both file in the same directory for get the result if one of the file is missing from the directory you can not get the proper result.

You also can write a formula by doing this, put “=” in a cell then select the cell which value you want and press enter you formula is written automatically.