Create a Timesheet in 60 minute format

[SarahM1985]

I need to create an excel timesheet to keep track of my temp employee.

Details...

• Monday - Friday Work Week
• 1 hour unpaid lunch
• Need to keep track of total hours for week (in 60 minute formula not 100) Example: need it to say 7:50 instead of 7.83
• Also, Since she is temp, her contract is for 720 hours before she can be hired on. I need to keep track of how many hours she has left.

What I have so far...

• Col A is Date Worked
• Col B is Time In
• Col C is Time Out
• Col D is Days Total (how many hours she worked that day
• Col E is Weeks Total
• Col F will reflect the 720 (preferable counting down. I.E. showing how many hours she has left)

• Col B and Col C is formatted in "Custom" H:mm
• Col D, I have the =24*(C2-B2+(C2>B2))-1

Col D is currently formatted as General, but I have tried Number (2 Decimal) and also the "Custom" h:mm (h:mm, for some reason, takes away my calculations and D2 goes to 0:00 and D3 goes to 20;00)


I do not have the formula for Col E or F yet, since I was distracted by D

 

[dmt32]

Hi welcome to forum,

have a look here:https://www.vertex42.com/ExcelTemplates/time-card-calculator.html

One of many sites that offers ready made timesheet templates you can download for free - you can either use asis or adapt to meet specific need.

Hope Helpful

Dave

 

[Gerald Higgins]

Hi, welcome to the board.

It looks like MAYBE you're not quite clear how times and dates work in Excel.

Let's look at your formula.

=24*(C2-B2+(C2>B2))-1

I think what you really want is simply
=C2-B2
which will give you the elapsed time between Time In and Time Out.
Remember to format the result into a time format.

If you want to take off a standard 1 hour for lunch, then it's something like this
=C2-B2-1/24

 

[SarahM1985]

Thank you. You are correct, I am quite new to using time in Excel, I have always used it for part numbers, pricing and inventory, but this is what management needs from me.

I have fixed my Formula to the one you suggested and it works great. Now my issues is adding the times up. I have use the =Sum(D3:D7) formula and have run into and issue. for 5 days of last week (Days totals: 6:50, 7:00, 7:00, 7:00, 7:00) my Weeks total came up as 10:50, which is obviously incorrect.

Online solutions has said to use the =Sum(D3:D7) formula, or =Sum(D3+D4+D5... Etc.

Please advice

and Thanks again

 

[Gerald Higgins]

The sum of 6:50, and 4 more days each of 7:00, is a total of 34:50 hours.

Which is equal to 1 whole day (24 hours) and 10:50 hours.

The issue you have is not with the formula, but with the FORMAT of the results.

Try formating the result as
[h]:mm

Also, I would recommend investigating how Excel deals with dates and times.

In short, Excel deals with dates and times using the value of 1 as a full day, 24 hours.

So any time span less than 24 hours is treated as a fraction of 1.
For example, 12 hours is 0.5, and 1 minute is 0.00069444... which is 1/1440 (there being 1,440 minutes in a day).

You then need to also understand the FORMAT of a cell.

For example, if you had a cell containing a value of 0.50069444...
if you apply a time format, such as hh:mm, this will be displayed as 12:01.

If the number of hours you have is more than 24, this can't be displayed with formats such as hh:mm, but [h]:mm gets round that.

 

[SarahM1985]

Thanks again, I figured out the issue and feel like an idiot. I was using hh:mm instead of [hh]:mm. I know better lol.

Anyway, thank you for your help.