Thread: 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

Originally Posted by daddylonglegs

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