• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to assign numbers based on the time slot

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,
I have a column with values such as ‘6/1/2016 6:05:50 AM’. And I have two conditions.

If time is between ‘6.30pm - 8.29 AM’ then 1 and if time is between ‘8.30 am - 6.29 PM’ then 2

I am confused about how do I decide that time I am trying to slot is in AM or PM
For example, 06:15 can be either PM or AM also. Looked simple when I started, got confused after some time.

Can anyone please help me in this.
 
Hi ,

More confusing is the problem description !

The example of 6/1/2016 6:05:50 AM does not correlate with the example of 06:15 ; is your data having such variations ?

Is the data sorted ?

Narayan
 
Hi @NARAYANK991 sir, extremely sorry for confusion.

No, data does not have such variation. I am trying to derive hour and minute from this data. All the values are in '6/2/2016 6:19:09 AM' format and sorted.
 
Hi Sachin ,

Then where is the problem ?

To decide whether the time is between 08:30 AM and 06:29 PM , it is easy ; to decide whether it falls in the other slot , check whether it does not fall in the above slot.

1. =MOD(A1,1)

isolates the time component of the value in A1.

2. =IF(AND(B1 >= ("08:30" + 0), B1 < ("18:30" + 0)), 2, 1)

uses the above time component value.

Narayan
 
Hi Sachin ,

06:05 can represent either AM or PM only when you look at it with your eyes.

When we see how Excel has stored it , it becomes clear whether it is AM or PM.

06:05 AM will be stored as 0.253472222222222 when you change the cell format to General.

06:05 will represent 06:05 PM only when the value is shown to be 0.753472222222222 when the cell format is changed to General.

The point is that we need not bother about whether 06:05 is AM or PM ; just compare it with the threshold values of 08:30 AM and 06:29 PM , and Excel will report the correct result.

Narayan
 
Hi @NARAYANK991 sir, I was trying to derive Hour and Minute using '=HOUR(A2) and '=MINUTE(A2).

This way I got tangled between AM or PM. Sir, I am trying to understand why do we use 1 in '=MOD(A1,1)'. When I tried '=MOD(A1,4'. I am getting the same result.

Thanks for the help. :)

I was not aware about "06:05 AM will be stored as 0.253472222222222".
Till date I only knew that dates are stored as numbers. Never paid attention to time values. My mistake.
 
Hi Sachin ,

You may have got the same result , but that will happen sometimes , not every time.

When you use MOD(value , 1) what you are doing is to isolate the decimal portion of a number.

Since times which have a date component are composed of an integer part which is the date , and a decimal part which is the time , to isolate the time part , we need to remove the integer part.

For example , suppose we use the NOW function to give us a value , we may get something like 20-06-2016 19:01 , which when we change the cell format to General shows us a value such as 42541.79256 , where 42541 is the date component 20-06-2016 , and 0.79256 is the time component 19:01.

When you take MOD(value , 4) it all depends on whether the date component is divisible by 4 ; if this is the case , then using =MOD(value , 4) will return the same result as using =MOD(value , 1).

When this is not the case , =MOD(value , 4) will return a value which is greater than 1 ; in such cases , the comparison with the two time thresholds will give the wrong result.

Narayan
 
You must log in or register to reply here.
Back
Top