New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Time sheet problem when employees times pass midnight.

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Time sheet problem when employees times pass midnight.

Postby Moonsheep » Sun May 29, 2016 7:03 am

Hi all,

I am creating a spreadsheet for a staff roster. The sheet works great, employee's hours are calculated and everything works fine.....Until I roster an employee to finish past midnight.

I did some googling and found a formula which is : =MOD((D10-C10),1) , where D10 is the finish time and C10 is the start time, but I still have 2 issues.

- Firstly in my total hours worked for the day (which is every employees individual hours totalled) it doesn't add together properly.

- Secondly, I need a formula that uses IF so that the sheet can figure out if an employee has worked and ended before midnight and us the normal calculation or if they worked past midnight and it needs to use the MOD formula. Im assuming it would be something like =IF([finish time]-[start time]<0 then MOD (([finish time]-[start time]),1

I need the sheet to understand if its a midnight sheet by itself so that I dont need to create a whole extra section for people that work past midnight.

If anyone could help, I'd appreciate it :)
  • 0

Moonsheep
Rookie
 
Posts: 3
Joined: May 29, 2016
Reputation: 0
Excel Version: 2013

Re: Time sheet problem when employees times pass midnight.

Postby gebobs » Tue May 31, 2016 10:27 am

Please attach a sample sheet.
  • 0

gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010

Re: Time sheet problem when employees times pass midnight.

Postby gebobs » Tue May 31, 2016 10:37 am

Moonsheep wrote:Firstly in my total hours worked for the day (which is every employees individual hours totalled) it doesn't add together properly.


It should. Each result, regardless of whether the end is before or after midnight, will be a decimal representing the fraction of a day that is the difference between the two times.

Secondly, I need a formula that uses IF so that the sheet can figure out if an employee has worked and ended before midnight and us the normal calculation or if they worked past midnight and it needs to use the MOD formula.


You should be able to use the same formula regardless.
  • 0

gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010

Re: Time sheet problem when employees times pass midnight.

Postby Moonsheep » Tue May 31, 2016 6:53 pm

Hi Gebobs,

Thanks for replying to my problem.

Below is the issue that I am having, attached the sheet also.

The first peter is using a simple [end time]-[start time]. The second one uses the MOD. I need the sheet to recognise if it is a negative number to work out the proper time, in this case 5 hours. Becasue its a sheet of mixed staff and times.

Maybe its not a mod formula that i need to use?


Peter 20:00 1:00 -19.00
Peter 20:00 1:00 5:00
Total -18.79
  • 0

You do not have the required permissions to view the files attached to this post.
Moonsheep
Rookie
 
Posts: 3
Joined: May 29, 2016
Reputation: 0
Excel Version: 2013

Re: Time sheet problem when employees times pass midnight.

Postby Moonsheep » Wed Jun 01, 2016 7:21 am

Basically, I dont actually know what the IF formula is that i should be using here. Cheers
  • 0

Moonsheep
Rookie
 
Posts: 3
Joined: May 29, 2016
Reputation: 0
Excel Version: 2013


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 24 guests

cron