Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Problem calculating Days/Hours etc between 2 dates
I want to calculate how many Days/Hours/Minutes/Seconds between Now and a future date eg how many Days/Hours/Minutes/Seconds between Now and 08:25 on 9th September 2005 I have used the formula below =(INT(B7-TODAY())-(MOD(B7,1)-MOD(NOW(),1)<0))&" Days "&TEXT(MOD(B7-NOW(),1),"hh")&" Hours "&TEXT(MOD(D6-NOW(),1),"mm") &" Minutes "&TEXT(MOD(D6-NOW(),1),"ss") &" Seconds " This returns the result 32 Days 14 Hours 01 Minutes 58 Seconds , which is wrong as there are 34 Days until 9th September, what is wrong with my formula? When I F9 to update only the seconds are changing Second question is there any way to automate this so that it counts down on the screen without having to recalculate Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=393641 |
#2
|
|||
|
|||
Hi Paul,
You can try this: =DATEDIF(NOW(),B7,"d")&" Days "&TEXT(B7-NOW(),"hh ""Hours ""mm ""Minutes ""ss ""Seconds""") Regards, KL "Paul Sheppard" wrote in message news:Paul.Sheppard.1tdzma_1123405501.6905@excelfor um-nospam.com... I want to calculate how many Days/Hours/Minutes/Seconds between Now and a future date eg how many Days/Hours/Minutes/Seconds between Now and 08:25 on 9th September 2005 I have used the formula below =(INT(B7-TODAY())-(MOD(B7,1)-MOD(NOW(),1)<0))&" Days "&TEXT(MOD(B7-NOW(),1),"hh")&" Hours "&TEXT(MOD(D6-NOW(),1),"mm") &" Minutes "&TEXT(MOD(D6-NOW(),1),"ss") &" Seconds " This returns the result 32 Days 14 Hours 01 Minutes 58 Seconds , which is wrong as there are 34 Days until 9th September, what is wrong with my formula? When I F9 to update only the seconds are changing Second question is there any way to automate this so that it counts down on the screen without having to recalculate Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=393641 |
#3
|
|||
|
|||
=INT(B7-TODAY())&" Days "&TEXT(MOD(B7-TODAY(),1),"hh")&" Hours
"&INT(MOD(MOD(B7-TODAY(),1)*24,1)*60)&" Minutes "&ROUND(MOD(MOD(MOD(B7-TODAY(),1)*24,1)*60,1)*60,0)&" Seconds " The second part is more complex as you will need APIs to link to a timer so that it doesn't tie up your machine. See here for an example http://tinyurl.com/c3xu5 -- HTH RP (remove nothere from the email address if mailing direct) "Paul Sheppard" wrote in message news:Paul.Sheppard.1tdzma_1123405501.6905@excelfor um-nospam.com... I want to calculate how many Days/Hours/Minutes/Seconds between Now and a future date eg how many Days/Hours/Minutes/Seconds between Now and 08:25 on 9th September 2005 I have used the formula below =(INT(B7-TODAY())-(MOD(B7,1)-MOD(NOW(),1)<0))&" Days "&TEXT(MOD(B7-NOW(),1),"hh")&" Hours "&TEXT(MOD(D6-NOW(),1),"mm") &" Minutes "&TEXT(MOD(D6-NOW(),1),"ss") &" Seconds " This returns the result 32 Days 14 Hours 01 Minutes 58 Seconds , which is wrong as there are 34 Days until 9th September, what is wrong with my formula? When I F9 to update only the seconds are changing Second question is there any way to automate this so that it counts down on the screen without having to recalculate Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=393641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with a formula for calculating based on a rage of dates | Excel Discussion (Misc queries) | |||
Calculating differences in dates | Excel Discussion (Misc queries) | |||
calculating with dates | Excel Worksheet Functions | |||
Calculating for number of days when values are in dates | Excel Worksheet Functions | |||
Problem with dates | Excel Worksheet Functions |