+ Reply to Thread
Results 1 to 4 of 4

IF Statements Involving Time Formatted Cells

Hybrid View

  1. #1
    Registered User
    Join Date
    02-07-2013
    Location
    West Virginia
    MS-Off Ver
    Excel 2007
    Posts
    7

    IF Statements Involving Time Formatted Cells

    Here’s my best shot at describing my problem. I am setting up a time card for tracking employee work hours. I want to include a rolling total of accumulated sick time, which is accrued at 5 hours 32 minutes (5:32) per pay period. Everyone has a pre-existing balance they’ve accumulated since they’re initial employment. Their total balance cannot exceed 1024 hours (1024:00). In order to track ongoing usage there is also a cell for entering usage for the current period and one for balance for the period. My problem comes from h:mm format and an IF statement regarding the maximum allowable hours (1024:00).

    Cell M13 is defined as the additional time (5:32) accrued per pay period.
    Cell M14 is the pre-existing balance of accrued time prior to this spreadsheet
    Cell M15 is the amount of sick time used within the current pay period. There are two defined uses for sick time. One being excused sick time and the other being personal days. The IF formula given below SUMS all the “P” personal and sick time for the pay period.
    Cell M16 calculates the ending pay period balance, as given below. As far as calculating the balance everything works. When I try to limit it to the 1024:00 maximum allowable is where the problem starts.

    M13 = 5:32 (formatted as [h]:mm)
    M14 = Pre-Existing Balance (formatted as [h]:mm)
    M15 = F29+SUMIF(J13:J19,"P",I13:I19)+SUMIF(J21:J27,"P",I21:I27) (formatted as [h]:mm and all associated time values formatted the same)
    M16 = calculated as M14-M15+M13 works as expected (correctly) when attempting to limit or cap the value to 1024:00 using IF(P23<TIMEVALUE("1024:00"),"M14-M15+M13","1024:00") it is an “EPIC FAILURE”!!!

    Any suggestions??? Thanks in advance for your time and assistance!!!

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: IF Statements Involving Time Formatted Cells

    Well, the first thing is "M14-M15+M13" will return exactly that string,that's what quotes do..
    I'd change the formula to this:
    =IF(P23<(1024/24),M14-M15+M13,1024/24)

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    02-07-2013
    Location
    West Virginia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: IF Statements Involving Time Formatted Cells

    Oops on the "" around the formula M14-M15+M13. In actuality I had tried it both ways, but unintentionally included them in my description.

    As for your suggestion, "EXCELLENT"! I greatly appreciate the assistance. You are Hero for the Day!!!

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: IF Statements Involving Time Formatted Cells

    You are welcome

    Please remember to mark the thread as solved if you are satisfied with your solution :
    To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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