Help with time formula please

**jenn2921** · 03-15-2013, 10:39 AM

I am trying to find a formula to keep track of how late my employees are to work. I am not super computer literate so I have tried what I know but nothing works. I would like it to calculate off of the set time they are supposed to clock in. For example, in A1 I have employee name (Michelle), B1 the time she clocked in (7:01), in C1 I would like a formula to show the time difference between 7:01 and 6:45 (the time she should have clocked in). I have four employees I would like to do this for, all have the set clock in time of 6:45. Everything I have tried gives me weird answers instead of displaying 0:16. Is this even possible?

Thanks in advance for your help.

**dilipandey** · 03-15-2013, 10:43 AM

Hi jenn,

welcome to the forum.
Can you share a sample workbook along with your expected results.. thx

Regards,
DILIPandey
<click on below * if this helps>

**Richard Buttrey** · 03-15-2013, 10:45 AM

Hi,

What formula are you using?
With a time of 6:45 in A1, and 7:01 in B1, then in C1, =B1-A1 will result in 0:16 assuming the formatting of C1 is a time format. What 'weird' numbers are you getting?

**FDibbins** · 03-15-2013, 10:46 AM

Hi and welcome to the forum

Try this, copied down...
=B1-TIME(6,45,0)

**jenn2921** · 03-15-2013, 11:32 AM

This is an example of the data I am entering. It is the "min past 6:45" column I want to see if there is a formula for.

6:45 MICHELLE MIN PAST 6:45
2/12/2017 6:58 0:13
2/13/2017 6:55 0:10
2/14/2017 9:00 0:15
2/15/2017 6:45 0:00
2/16/2017 6:54 0:09
WKLY TOTAL 0:47

I changed the format of the cells and the answer now looks right, thanks Richard!

Is there way to always have the one cell locked in a formula? For example, I put 6:45 in A1 and the formula of =B2-A1 in C2 to get the answer of 0:13 but when I tried to copy the formula to C3, it changed the formula to =B3-A2 when I need it to say =B3-A1. How do I lock the A1 in place?

**Richard Buttrey** · 03-15-2013, 11:46 AM

Hi,

Making cells absolute vs relative is basic to Excel.
To 'lock' a reference preface the row number or column letter or both with a $ sign.
i.e.
=$A$1