Time and Working hours Non-working hours

[KVAMSHE]

Hi Chandoo,


I'm looking for two formula as per my requirement given below:


1. From datetime format (1/1/2013 12:51:13 AM) - how to extract only time into a separate cell?


2. Our shift start at 6AM & ends at 2AM. Ex: If i start my shift on Monday morning at 6AM it goes upto 2AM on Tuesday early morning. Based on this shift timings i want to define working hours and Non-working hours from (06:00:00 AM to 06:00:00 AM i.e.24 hrs).


Please help me with both formulas. In case of any question/s please revert.


Regards,

Vamshe

 

[Hui]

Vamshe


1.

The time is the fractional part of the cells value

=A1-Int(A1)


2. Not sure what the question is here?

If your times are in A1 1/1/2013 6:00:00 AM

and A2 2/1/2013 2:00:00 AM

It is simply =A2-A1

The formula cell should be formatted as [h].mm.ss

etc

 

[KVAMSHE]

Hi Hui,


Sorry for unclear question. Please refer to description of my requirement below:


I want to calculate no.of issues logged by customers during working hours and un-working hours where my working hours in a day is 20 hours out of 24 hrs because our team support critical issues.


Working hours - 6:00 AM to 02:AM (3rd June to 4th June)

Non-working hours - 2:00AM to 6:00 AM (4th June)

Ex:Monday morning at 6:00AM we start our day and ends by Tuesday early morning 02:00AM. Therefore 24hours of a day for us comes between two dates.


As per SLA we need to perform the ticket within 1hr from the time ticket is submitted by customer. Therefore if any ticket submitted by customer at closing hours i.e. at 2AM early morning then that ticket will be performed after 4 hours because from 2:00AM to 6:00AM is our Non-working hours. From the given scenario we want to calculate average time take to perform tickets submitted during working hours and non-working hours. So to do this, 1st we need to classify whether ticket is submitted during working hours or non-working hours based on submitted date (i.e. 1/1/13 12:51 AM).

Hope this is clear!

 

[KVAMSHE]

Forgot to mention in previous mail.


Adding to previous description,


Working days = start from Monday 6:00AM to Saturday 2:00AM

Non-working days = Saturday 2:00AM to Monday 6:00 AM


Therefore i want a formula to classify the ticket submitted dates as working hours & working days and non-working hours and non-working days.


Regards,

Vamshe

 

[Faseeh]

Hi KVAMSHE,


With your data in A2 as this "1/7/2013 6:00:00 AM", try this formula:

=IF((A2-INT(A2)>=0.25)*(A2<=A2+TIME(20,0,0))*(AND(WEEKDAY(A2,1)>=2,WEEKDAY(A2,1)<=6)),"Ok","No")

Regards,

 

[NARAYANK991]

Hi Vamshe ,


Can you check this file ?


https://www.dropbox.com/s/tq59iutmayl4feq/Vamshe_Book1.xlsx


Narayan

 

[saro23]

Hi All,


I'm looking for a formula that should work in 12 hrs ie. AM &PM


if the working hours is more than 12 hrs then the employee can enjoy full allowance

if not only he can enjoy 70% of the allowance.


regards,

saro23

 

[SirJB7]

@saro23

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it has a month yet. If needed you could add a reference in your new one.

Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!

PS: Please don't answer here at this thread.