Use Excel to calculate when it’s time to go home.
Most computers read dates and time the same way we read numbers. For instance today, January 16, 2015 is day number 42020 counting from the “beginning of time” which was January 1, 1900 (from a computer’s point of view)
Today at noon the computer time will be 42020.5, half way through the day (.5)
Why don’t you give it a try: Open Microsoft Excel and enter 42020 in a cell. Then format the cell with a date format.
Or reverse it: enter today’s date in a cell then format it as a number.
(You can apply formats by going to the Home Tab and then select the Format Number drop down)
42020 = 1/16/2015
Audrey a student in my class this week, asked a great question.
How would I build a formula that calculates when I will have completed my 8 hours of work for the day and displays the time for me to leave?
For instance:
If I clock in at 8:00am then clock out at 12:00pm for lunch. That would be 4 hours of work
Then I clock back in at 12:30pm after lunch. Based on these times and the requirement to work 8 hours, it would be okay for me to leave at 4:30pm.
Easy enough when we keep it simple with on-the-hour or on-the-half hour, but it gets more difficult when we use more realistic times:
Clock in at 7:53
Go to lunch at 11:41
Return from lunch at 12:33
I need to put in 8 hours, so when can I leave?
Here is the formula to make it happen for you Audrey
Let’s start with simple times:
Enter 8:00 am in cell A2
Enter 12:00 pm in cell B2
Enter 1:00 pm in cell C2
Enter this formula in cell D2:
=0.33333-(B2-A2)+C2
Format the cells to display as Time
Select the 4 cells and then go to the Home tab and select the drop down next to the Number Format Selector and select “Time”
When you change the time for Clock In, Clock Out Lunch, Clock In Lunch, the Clock Out time will be calculated for you.
There you go…
I have included a link to an Excel file with a working formula. Just click the link and download the file…
https://www.dropbox.com/s/fv2d5tusde44pkj/Clock%20In%20Out%20Time%20Calc.xlsx?dl=0
Enjoy