It’s time for you to go home, Audrey

Punch In clock

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)

Number Format

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

Time Calculation

Format the cells to display as Time

Number Format

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”

Time Calculation

When you change the time for Clock In, Clock Out Lunch, Clock In Lunch, the Clock Out time will be calculated for you.

Clock In Real Times

There you go…

I have included a link to an Excel file with a working formula.  Just click the link and download the file…

Clock Out Time File Link

https://www.dropbox.com/s/fv2d5tusde44pkj/Clock%20In%20Out%20Time%20Calc.xlsx?dl=0
Enjoy

Best Advice for Data in Excel

Microsoft Excel Logo

Clean it up.

In 3 minutes I will teach you the best way to clean your data for improved sorting, filtering, better Pivot Tables and data analysis. Watch the video. Share it with others. Improve your life. Make new friends. All in 3 minutes. Enjoy