Minutes Calculation

Hi All,

I would like to derive time taken to resolve a ticket. For example if the ticket has been resolved within 5 minutes, i would be posted as "<=05 Mins", based on difference of datetime between two date time field (start time and end time) of ticket.

Sample Data:, excel file is attached.


Other values should be populated based on this table below.

<=05 Mins.
<=10 Mins.
<=15 Mins.
<=20 Mins.
<=25 Mins.
<=30 Mins.
<=1 hour.
<=2 hours.
<=4 hours.
<=8 hours.
<=24 hours.
<=2 days.
<=5 days.
<=7 days.
<=15 days.
<=31 days.
>=One Month.

Current Formula:

=IF(Z30053="NA","NA",
IF(Z30053="Open","Open",IF(Z30053<>"Open",
IF(AND(INT((Z30053-Y30053)*1440)>0,(INT((Z30053-Y30053)*1440)<=5)),"<=05 Mins",
IF(AND(INT((Z30053-Y30053)*1440)>6,(INT((Z30053-Y30053)*1440)<=10)),"<=10 Mins",
IF(AND(INT((Z30053-Y30053)*1440)>11,(INT((Z30053-Y30053)*1440)<=15)),"<=15 Mins",
IF(AND(INT((Z30053-Y30053)*1440)>16,(INT((Z30053-Y30053)*1440)<=20)),"<=20 Mins",
IF(AND(INT((Z30053-Y30053)*1440)>21,(INT((Z30053-Y30053)*1440)<=20)),"<=25 Mins",
IF(AND(INT((Z30053-Y30053)*1440)>26,(INT((Z30053-Y30053)*1440)<=30)),"<=30 Mins",
IF(AND(INT((Z30053-Y30053)*1440)>31,(INT((Z30053-Y30053)*1440)<=45)),"<=45 Mins",
IF(AND(INT((Z30053-Y30053)*1440)>46,(INT((Z30053-Y30053)*1440)<=60)),"<=60 Mins",
IF(AND(INT((Z30053-Y30053)*1440)>61,(INT((Z30053-Y30053)*1440)<=120)),"<=2 hours",
IF(AND(INT((Z30053-Y30053)*1440)>121,(INT((Z30053-Y30053)*1440)<=240)),"<=4 hours",
IF(AND(INT((Z30053-Y30053)*1440)>241,(INT((Z30053-Y30053)*1440)<=480)),"<=8 hours",
"More than 1 Day"))))))))))))))

Please let me know if this has better formula?

Kindest Regards,
VCP Muthukrsihna

ALM means Alarm
ACK means Acknowledgement