+ Reply to Thread
Results 1 to 5 of 5

IF statement to calculate working days and hours fomatted as "1 day, 4.5 hours"

  1. #1
    Registered User
    Join Date
    11-30-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    IF statement to calculate working days and hours fomatted as "1 day, 4.5 hours"

    Hi,

    I'm hoping this has an easy answer and thanks in advance for any help!!

    I have (almost) sucessfully completed a new spreadsheet to count the number of working days and hours from the start of a job to its finish.

    The start and finish dates/times are in the format: 07/11/2011 15:00:00
    The results (time from start to finish) are in the format: 1 days 4.5 hours

    In order to measure the SLA I used a simple IF statement which adds either 0% or 100% to a new column depending on whether the SLA has been met or not:
    =IF(H3<G3, 100%, 0%)

    This works absolutely fine until i get into the tens of days.

    As an example (data below in order of - start, finish, SLA, result, result of above IF statement):
    15/11/2011 09:00, 16/11/2011 12:30, 2 days 0 hours, 1 days 3.5 hours,100% [CORRECT]
    10/11/2011 15:30, 02/12/2011 10:30, 2 days 0 hours, 15 days 3 hours,100% [WRONG]
    12/11/2011 15:30, 22/11/2011 10:30, 2 days 0 hours, 6 days 3 hours, 0% [CORRECT]

    I think that excel is just counting the first number, ie. the "1" of "15" and deciding that this is less then the required SLA (2 days 0 hours) and therefore putting 100% in the column.

    If i change the date to either 20 or 30 days then it works again, so it's only when the result is in the tens.

    Does anyone know if i can get an IF statement to work correctly on this day, hour fomatting?

    Thanks,
    Retro888

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: IF statement to calculate working days and hours fomatted as "1 day, 4.5 hours"

    Your comparison will always be more complex if you are comparing text strings. Why not put the SLA figure in time format, e.g. for an 8 hours day then 2 days would be just 16:00.

    ...then calculate the working hours in time too, then you can just do a simple comparison.

    What formula are you using currently to claculate the working days/hours?
    Audere est facere

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF statement to calculate working days and hours fomatted as "1 day, 4.5 hours"

    If your cell contains "1 days 3.5 hours" then yes, only the first character in the string is being compared.

    Either split "1 days 3.5 hours" into two columns, one for days and one for hours or
    use helper columns, ie store the days and hours in two columns (hide them if necessary) but use the SLA % formula against the unformatted (hidden) columns then the calculation should be correct.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    11-30-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: IF statement to calculate working days and hours fomatted as "1 day, 4.5 hours"

    Quote Originally Posted by daddylonglegs View Post
    Your comparison will always be more complex if you are comparing text strings. Why not put the SLA figure in time format, e.g. for an 8 hours day then 2 days would be just 16:00.

    ...then calculate the working hours in time too, then you can just do a simple comparison.

    What formula are you using currently to claculate the working days/hours?
    Thanks Daddylong legs.
    I would if i could! The formula i used was a version i managed to get working after some forum searching, but i haven't been able to change the formatting. I admit, it's well over my knowledge!

    I have attached the spreadsheet as it is to see if you have any better luck!
    Many thanks,
    retro888
    Attached Files Attached Files

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: IF statement to calculate working days and hours fomatted as "1 day, 4.5 hours"

    I would change the values in G2:G5 of sheet2 to show simple time values like 8:00, 16:00, 4:00 and 4:00 then those times will also be populated into Data worksheet G3:G8.

    Now use this formula in H3 copied down to get the business hours taken

    =(NETWORKDAYS(StartDT,EndDT,HolidayList)-1)*(DayEnd-DayStart)+MOD(EndDT,1)-MOD(StartDT,1)

    Formula in I3:I8 should now work OK

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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