Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
number of hours between two date/times
I have tried a number of the responses posted- sorry i can't seem to get it
right! I want to calculate the number of days and hours between two date time values e.g. cell A1 start: 17/01/2006 16:12 cell A2 finish: 25/01/2006 10:00 cell A3 answer: 7 days 18 hrs and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format rather than 'general'? thanks for your help- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
number of hours between two date/times
=INT(A2-A1)&" days "&ROUND(MOD(A2-A1,1)*24,0)&" hours"
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "TBA" wrote in message ... I have tried a number of the responses posted- sorry i can't seem to get it right! I want to calculate the number of days and hours between two date time values e.g. cell A1 start: 17/01/2006 16:12 cell A2 finish: 25/01/2006 10:00 cell A3 answer: 7 days 18 hrs and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format rather than 'general'? thanks for your help- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
number of hours between two date/times
Thanks Bob- such prompt service!
that works fine. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
number of hours between two date/times
=TEXT(INT(A2-A1),"#")&" Days "&TEXT(24*(MOD(A2-A1,1)),"#")&" hrs."
this formula outputs the total number of days, and rounds the remaining hours (18 hrs instead of 17 hrs 48 minutes). You might be having trouble with the way you are inputing your dates. If you format them to general you should get numbers like this. 38734.675 38742.41667 This is the actual serial number for the dates you specified. If they stay in date format, that means the cells are actually text strings and not dates (as far as excel is concerned). You might need to switch the day and month, and then change the format to dd/mm/yyyy hh:mm to get them to show how you want. "TBA" wrote: I have tried a number of the responses posted- sorry i can't seem to get it right! I want to calculate the number of days and hours between two date time values e.g. cell A1 start: 17/01/2006 16:12 cell A2 finish: 25/01/2006 10:00 cell A3 answer: 7 days 18 hrs and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format rather than 'general'? thanks for your help- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
number of hours between two date/times
Bob,
you make me look so stupid :). You posted and got a response in the time it took me to finish writing my post. Jason Rackley "Bob Phillips" wrote: =INT(A2-A1)&" days "&ROUND(MOD(A2-A1,1)*24,0)&" hours" -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "TBA" wrote in message ... I have tried a number of the responses posted- sorry i can't seem to get it right! I want to calculate the number of days and hours between two date time values e.g. cell A1 start: 17/01/2006 16:12 cell A2 finish: 25/01/2006 10:00 cell A3 answer: 7 days 18 hrs and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format rather than 'general'? thanks for your help- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
number of hours between two date/times
thanks Sloth, i will try this too
cheers Theo "Sloth" wrote: =TEXT(INT(A2-A1),"#")&" Days "&TEXT(24*(MOD(A2-A1,1)),"#")&" hrs." this formula outputs the total number of days, and rounds the remaining hours (18 hrs instead of 17 hrs 48 minutes). You might be having trouble with the way you are inputing your dates. If you format them to general you should get numbers like this. 38734.675 38742.41667 This is the actual serial number for the dates you specified. If they stay in date format, that means the cells are actually text strings and not dates (as far as excel is concerned). You might need to switch the day and month, and then change the format to dd/mm/yyyy hh:mm to get them to show how you want. "TBA" wrote: I have tried a number of the responses posted- sorry i can't seem to get it right! I want to calculate the number of days and hours between two date time values e.g. cell A1 start: 17/01/2006 16:12 cell A2 finish: 25/01/2006 10:00 cell A3 answer: 7 days 18 hrs and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format rather than 'general'? thanks for your help- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
number of hours between two date/times
Here's another way....:) =TEXT(ROUND((A2-A1)*24,0)/24,"d \da\y\s h \hr\s") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=505530 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
number of hours between two date/times
The OP is spoilt. 47 minutes and three alternative solutions. Where else do
you get (free) service like that? Bob "Sloth" wrote in message ... Bob, you make me look so stupid :). You posted and got a response in the time it took me to finish writing my post. Jason Rackley "Bob Phillips" wrote: =INT(A2-A1)&" days "&ROUND(MOD(A2-A1,1)*24,0)&" hours" -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "TBA" wrote in message ... I have tried a number of the responses posted- sorry i can't seem to get it right! I want to calculate the number of days and hours between two date time values e.g. cell A1 start: 17/01/2006 16:12 cell A2 finish: 25/01/2006 10:00 cell A3 answer: 7 days 18 hrs and is it possible to leave cells A1 and A2 in dd:mm:yy hh:mm format rather than 'general'? thanks for your help- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting a decimal number to hours and seconds | Excel Worksheet Functions | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
How do I convert a number of minutes into hours and minutes? | Excel Worksheet Functions | |||
Sumproduct help needed! | Excel Worksheet Functions | |||
how to convert total hours (HH:MM) into decimal number | Excel Worksheet Functions |