Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Difference in Hours
Hi all,
I have to different Date time values. That are Received Date time value and Informed Date time Value. Both the values are customised in the format as follows: dd.mm.yyyy hh:mm:ss AM/PM Now, i want to find out the difference in hours between two dates in excel. I know how to do it in Access, but not getting through in excel the same formula. For example Received date is 19.04.2005 11:30:00 AM and Informed Date is 21.04.2005 12:30:00 PM the result should be 49, that is in hours. Any help will be highly appriciated. Thanks in advance Karan. |
#2
|
|||
|
|||
Just multiply the result by 24
=(A7-A6)*24 if you only want hours, no minutes fraction use =INT((A7-A6)*24) -- HTH RP (remove nothere from the email address if mailing direct) "Karan" wrote in message ... Hi all, I have to different Date time values. That are Received Date time value and Informed Date time Value. Both the values are customised in the format as follows: dd.mm.yyyy hh:mm:ss AM/PM Now, i want to find out the difference in hours between two dates in excel. I know how to do it in Access, but not getting through in excel the same formula. For example Received date is 19.04.2005 11:30:00 AM and Informed Date is 21.04.2005 12:30:00 PM the result should be 49, that is in hours. Any help will be highly appriciated. Thanks in advance Karan. |
#3
|
|||
|
|||
Thanks for your valuable help. It works great. But, now i realise that it
should exclude sundays in between two date time value's. Is it posible to do it, if yes please let me know. Once again thanks for your tips. Karan. "Bob Phillips" wrote: Just multiply the result by 24 =(A7-A6)*24 if you only want hours, no minutes fraction use =INT((A7-A6)*24) -- HTH RP (remove nothere from the email address if mailing direct) "Karan" wrote in message ... Hi all, I have to different Date time values. That are Received Date time value and Informed Date time Value. Both the values are customised in the format as follows: dd.mm.yyyy hh:mm:ss AM/PM Now, i want to find out the difference in hours between two dates in excel. I know how to do it in Access, but not getting through in excel the same formula. For example Received date is 19.04.2005 11:30:00 AM and Informed Date is 21.04.2005 12:30:00 PM the result should be 49, that is in hours. Any help will be highly appriciated. Thanks in advance Karan. |
#4
|
|||
|
|||
Hi Karan,
Try this =(A7-A6-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A6)&":"&INT(A7))))=1)))* 24 -- HTH RP (remove nothere from the email address if mailing direct) "Karan" wrote in message ... Thanks for your valuable help. It works great. But, now i realise that it should exclude sundays in between two date time value's. Is it posible to do it, if yes please let me know. Once again thanks for your tips. Karan. "Bob Phillips" wrote: Just multiply the result by 24 =(A7-A6)*24 if you only want hours, no minutes fraction use =INT((A7-A6)*24) -- HTH RP (remove nothere from the email address if mailing direct) "Karan" wrote in message ... Hi all, I have to different Date time values. That are Received Date time value and Informed Date time Value. Both the values are customised in the format as follows: dd.mm.yyyy hh:mm:ss AM/PM Now, i want to find out the difference in hours between two dates in excel. I know how to do it in Access, but not getting through in excel the same formula. For example Received date is 19.04.2005 11:30:00 AM and Informed Date is 21.04.2005 12:30:00 PM the result should be 49, that is in hours. Any help will be highly appriciated. Thanks in advance Karan. |
#5
|
|||
|
|||
Fails if A& or A6 is a Sunday, this doesn't
=(A7-A6-IF(WEEKDAY(A6)=1,1-MOD(A6,1),IF(WEEKDAY(A7)=1,MOD(A7,1),SUMPRODUCT(- -(WEEKDAY(ROW(INDIRECT(INT(A6)&":"&INT(A7))))=1)))) )*24 -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Hi Karan, Try this =(A7-A6-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A6)&":"&INT(A7))))=1)))* 24 -- HTH RP (remove nothere from the email address if mailing direct) "Karan" wrote in message ... Thanks for your valuable help. It works great. But, now i realise that it should exclude sundays in between two date time value's. Is it posible to do it, if yes please let me know. Once again thanks for your tips. Karan. "Bob Phillips" wrote: Just multiply the result by 24 =(A7-A6)*24 if you only want hours, no minutes fraction use =INT((A7-A6)*24) -- HTH RP (remove nothere from the email address if mailing direct) "Karan" wrote in message ... Hi all, I have to different Date time values. That are Received Date time value and Informed Date time Value. Both the values are customised in the format as follows: dd.mm.yyyy hh:mm:ss AM/PM Now, i want to find out the difference in hours between two dates in excel. I know how to do it in Access, but not getting through in excel the same formula. For example Received date is 19.04.2005 11:30:00 AM and Informed Date is 21.04.2005 12:30:00 PM the result should be 49, that is in hours. Any help will be highly appriciated. Thanks in advance Karan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate hours just can't figure it out | Excel Worksheet Functions | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
How do I calculate difference in days & hours between two dates e. | Excel Worksheet Functions | |||
Subtracting paid hours from unpaid hours | Excel Worksheet Functions | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions |