+ Reply to Thread
Results 1 to 5 of 5

Rounding time to the closest 1/4 of the hour and add brake to the timesheet

  1. #1
    Registered User
    Join Date
    12-12-2013
    Location
    Winnipeg
    MS-Off Ver
    Excel 2010
    Posts
    9

    Rounding time to the closest 1/4 of the hour and add brake to the timesheet

    Hello to everyone!

    According the title of the issue I believe that you can understand what I need to do!
    I am trying to make a formula rounding the time to the closest 1/4 of the time.
    So, I have already done this, so far. My problem is how to put a brake on this formula.
    Could you please help me?
    Formula:
    =IF((ROUND((((C12-B12)+IF(C12<B12,1))*24)/0.25,0)*0.25)-0.5=5,ROUND((((C12-B12)+IF(C12<B12,1))*24)/0.25,0)*0.25,IF((ROUND((((C12-B12)+IF(C12<B12,1))*24)/0.25,0)*0.25)-0.5>=8,8,ROUND((((C12-B12)+IF(C12<B12,1))*24)/0.25,0)*0.25))

    I want to count and the "IN(2)" and the "OUT(2)". Also, in the formula I have a "-0.5" because I subtract the 1/2 hour of the brake.

    I am sending and my time sheet to take a look.

    Thank you very much in advanced! Please, reply to me as soon as you can!

    Example.xlsx

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Rounding time to the closest 1/4 of the hour and add brake to the timesheet

    Hi atzerv,

    It sounds like you need the MRound() function. Read about it at:
    http://excel.tips.net/T002186_Rounding_Time.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    12-12-2013
    Location
    Winnipeg
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rounding time to the closest 1/4 of the hour and add brake to the timesheet

    Thank you for your comment!
    I have read the Mround but I couldn`t make it work as I wanted. The result here is good! The rounding is as I wanted. The only thing that I want now is to add a Brake......

    Could you help me?

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Rounding time to the closest 1/4 of the hour and add brake to the timesheet

    Hi,

    See the attached where I'm not sure I changed all the formulas but did fix the ones that had numbers in them. See if this helps..
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-12-2013
    Location
    Winnipeg
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rounding time to the closest 1/4 of the hour and add brake to the timesheet

    Hi MarvinP,

    Thank you very much for your reply!
    I see you timesheet but the thing is that maybe I wasn't clear.
    I will try to describe it and I am sending again the timesheet with more values.

    So, the employee is coming for example at 8:00AM (Cell: B12) and then is living at 14:35PM (Cell: C12) (i.e. to pick up his child) and then is coming back to the work at 15:30 PM (Cell: E12) and finally is finishing his job at 17:30 PM (Cell: F12).
    I want at Cell G12 to calculate the time that the employee was at his office excluding the 0.5 Hour of brake (Every employee that he is working over 5hours per day will have 0.5 Hour lanch brake, which is excluding from the working hours) and also excluding the brake that he was out of the office (Between 14:35 PM till 15:30 PM)!

    Could you help me please?

    Thank you very much!
    Attached Files Attached Files
    Last edited by atzerv; 02-14-2014 at 11:48 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Rounding time to hour (only)
    By shepzep in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-18-2011, 03:51 PM
  2. Rounding Timesheet to quarter hour
    By djknight2007 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-30-2010, 01:12 PM
  3. Rounding time to nearest Hour
    By Peo Sjoblom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1