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
Please send us your comments and idea for make this blog better.
