Create a Timesheet in 60 minute format

SarahM1985

New Member
Joined
Mar 27, 2018
Messages
3
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Mar 27, 2018
Messages
3
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Mar 27, 2018
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,971
Messages
5,465,747
Members
406,448
Latest member
IrishDMan

This Week's Hot Topics

Top