• 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.

excel formulae to change date and time if the time exceeds 08.30 pm.

D

dev.sutradhar1987

Member
hi all


i want an excel formulae which will automatically change the date and time to next day and time should be 11.30am if the time exceeds 08.30pm..format dd.mm.yyyy hh.mm am/pm


could any one help me.
 
Hi Dev ,


Since 11:30 a.m. is 15 hours from 08:30 p.m. , when ever the entered time is beyond 08:30 p.m. , add 15 hours to the entered date + time.


For example , suppose the entered date + time is 2/17/2013 19:30 hrs ; this is less than 08:30 p.m. , which means the output will be the same. Suppose the date + time is 2/17/2013 22:15 hrs , the output should be 2/17/2013 22:15 + "15:00:00".


=IF(A1<=INT(A1)+"20:30:00",A1,A1+"15:00:00")


Narayan
 
Hi Narayank991,


Thank you very much for the reply.


The Formulae works but not exactly the way i want.


The problem is it is ading the time with the time which exceeds 20.30 PM.But what i want is if the time exceeds 20.30PM the time i want is next day 11.30 am no matter whta the time is exceeding 20.30.Pm.


Ex - If the time is 2/17/2013 20.45 PM, the time should be - 2/18/2013 11.30 AM

Again Time - 2/17/2013 21.15 AM, Time Should be 2/17/2013 11.30 AM


(Nothing should get added with the previous time)


Please help.
 
Hi Dev ,


In that case , change the formula to :


=IF(A1<=INT(A1)+"20:30:00",A1,A1+"15:00:00"-(MOD(A1,1)-"20:30:00"))


Can you clarify as to what will happen for a date and time combination such as :


2/17/2013 02:45


which 2 hours and 45 minutes past midnight ?


Narayan
 
Hi Dev ,


This is getting slightly complicated !


Can I take it that :


1. For any Date and Time between Date + "20:30:01" and Date + "23:59:59" , the output should be ( Date + 1 ) + "11:30:00"


2. For any Date and Time between Date + "00:00:00" and Date + "11:30:00" , the output should be Date + "11:30:00"


Narayan
 
Hi Narayan,


That is exactly what i want.


Actually i have created a tracker sheet and our working hours is from 11.30AM - 08.30PM. So Both the condition that you have given is perfect to what i need.
 
Hi Dev ,


Suppose we define two named ranges as follows :

[pre]
Code: 
From_Hours :  which will contain the time value 11:30:00

To_hours   :  which will contain the time value 20:30:00
[/pre]
then , the following formula can be checked :


=IF(A1<=INT(A1)+From_Hours,INT(A1)+From_Hours,IF(A1<=INT(A1)+To_Hours,A1,INT(A1)+1+From_Hours))


Narayan
 
Hi ,


Any date is actually a date + time combination , where the date is an integer number from 1 onwards , and the time is a decimal number between 0 and 1 , representing the time interval of 24 hours in a day.


Thus , suppose we take today's date and a time of 06:00:00 hrs ( 6:00 AM ) , when you enter this as 06/24/2013 06:00 AM ( or as 06/24/2013 06:00 ) , since my system date format is mm/dd/yyyy , Excel will display this in Time format. However , if you change the cell format to number with 5 decimal places , Excel will display 41449.25


Similarly , if you enter other values , as shown below , you will see the actual value that Excel stores is just a numeric value :

[pre]
Code: 
6/24/2013 6:00     41449.25000
6/24/2013 12:00    41449.50000
6/24/2013 18:00    41449.75000
6/24/2013          41449.00000
[/pre]
You can see that just the date is the same as the integer value of the date + time combination.


So , given any date + time combination , if we take the INT of the combination , the result is the date alone , without the time component.


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