Calculate SLA based on time formatted as [hh]:mm

[Ehtisham Ali]

Hi Guys,

I am stuck with a time format issue. I have a column containing time ([hh]:mm) based on which I want to calculate SLA's (YES/NO).

I am attaching a self explanatory macro based sheet for your reference.

Please suggest.

 

[Hui]

Change the line
from: dt_hr = Hour(Range("R" & i).Value)
to: dt_hr = Range("B" & i).Value * 24

What is happening is that Column B has Hrs and example B7 has 50:51
which is 2.11888888888643 days

for B7 The expression Range("R" & i).Value returns 2.11888
So simply multiply that by 24 to get 50.85 hrs and then do your checks

 

[Ehtisham Ali]

Change the line
from: dt_hr = Hour(Range("R" & i).Value)
to: dt_hr = Range("B" & i).Value * 24

What is happening is that Column B has Hrs and example B7 has 50:51
which is 2.11888888888643 days

for B7 The expression Range("R" & i).Value returns 2.11888
So simply multiply that by 24 to get 50.85 hrs and then do your checks

Hi Sir,

I have tried your suggestion but no help.

 

[Chihiro]

@Ehtisham Ali

FYI - You can't use Hour function on time greater than 24hrs. Well, you can, but it will return hour portion only and will ignore Day portion.

Ex. Hour("27:01") is extracting hour from 1 day 3 hours 1 min. Thus it will return 3 and not 27.

Hui's suggestion is the simplest way to deal with this issue. I tested it and result matched your criteria.

 

[Hui]

Works for me?


see attached file:

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[Ehtisham Ali]

Works for me?
View attachment 31804

see attached file:

oops Sorry

My mistake I had not remove "Hour" from the equation.

Thanks for giving clarification again.