ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Calculating days & time left from start date/time to end date/time (https://www.excelbanter.com/excel-worksheet-functions/59080-calculating-days-time-left-start-date-time-end-date-time.html)

marie

Calculating days & time left from start date/time to end date/time
 
Hello experts,
What formula should I use to show # days left, including time (in hrs,
mins and sec's) from current date/time to a target end date/time?
For instance:

Start Date: Now()
End Date: December 31, 2005 5:00:00 PM

Your help is greatly appreciated.
Marie


Sloth

Calculating days & time left from start date/time to end date/time
 
=ROUND(A2-A1,0)&" days and "&TEXT(A2-A1,"h:mm:ss")

Or if it is less than one month, you can just subtract them (=A2-A1) and use
a custom format of

d "days and" h:mm

They both look like this
25 days and 1:20:27

Bob Phillips

Calculating days & time left from start date/time to end date/time
 
How about

=INT(A21-NOW())&" days, "&TEXT(MOD(A21-NOW(),1)," hh:mm:ss")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"marie" wrote in message
...
Hello experts,
What formula should I use to show # days left, including time (in hrs,
mins and sec's) from current date/time to a target end date/time?
For instance:

Start Date: Now()
End Date: December 31, 2005 5:00:00 PM

Your help is greatly appreciated.
Marie




marie

Calculating days & time left from start date/time to end date/
 
This worked! Thanks!

"Sloth" wrote:

=ROUND(A2-A1,0)&" days and "&TEXT(A2-A1,"h:mm:ss")

Or if it is less than one month, you can just subtract them (=A2-A1) and use
a custom format of

d "days and" h:mm

They both look like this
25 days and 1:20:27


marie

Calculating days & time left from start date/time to end date/
 
Hi Bob, I compared the result using your formula with the one provided by
"Sloth" and it appears that I am off by a day using yours. 'Not sure why.


"Bob Phillips" wrote:

How about

=INT(A21-NOW())&" days, "&TEXT(MOD(A21-NOW(),1)," hh:mm:ss")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"marie" wrote in message
...
Hello experts,
What formula should I use to show # days left, including time (in hrs,
mins and sec's) from current date/time to a target end date/time?
For instance:

Start Date: Now()
End Date: December 31, 2005 5:00:00 PM

Your help is greatly appreciated.
Marie





Bob Phillips

Calculating days & time left from start date/time to end date/
 
That is because Sloth's Rounds the subtracted dates, so if it is 24.6 say,
it rounds it up to 25, and then the .6 is used to calculate the hours. So, I
think he is wrong

--

HTH

RP
(remove nothere from the email address if mailing direct)


"marie" wrote in message
...
Hi Bob, I compared the result using your formula with the one provided by
"Sloth" and it appears that I am off by a day using yours. 'Not sure why.


"Bob Phillips" wrote:

How about

=INT(A21-NOW())&" days, "&TEXT(MOD(A21-NOW(),1)," hh:mm:ss")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"marie" wrote in message
...
Hello experts,
What formula should I use to show # days left, including time (in

hrs,
mins and sec's) from current date/time to a target end date/time?
For instance:

Start Date: Now()
End Date: December 31, 2005 5:00:00 PM

Your help is greatly appreciated.
Marie







Bob Phillips

Calculating days & time left from start date/time to end date/
 
Try it with tomorrow at midday to see, Sloth's gives more than 1 day!

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
That is because Sloth's Rounds the subtracted dates, so if it is 24.6 say,
it rounds it up to 25, and then the .6 is used to calculate the hours. So,

I
think he is wrong

--

HTH

RP
(remove nothere from the email address if mailing direct)


"marie" wrote in message
...
Hi Bob, I compared the result using your formula with the one provided

by
"Sloth" and it appears that I am off by a day using yours. 'Not sure

why.


"Bob Phillips" wrote:

How about

=INT(A21-NOW())&" days, "&TEXT(MOD(A21-NOW(),1)," hh:mm:ss")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"marie" wrote in message
...
Hello experts,
What formula should I use to show # days left, including time (in

hrs,
mins and sec's) from current date/time to a target end date/time?
For instance:

Start Date: Now()
End Date: December 31, 2005 5:00:00 PM

Your help is greatly appreciated.
Marie









Sloth

Calculating days & time left from start date/time to end date/
 
oops. your correct bob. sorry about that marie.

"Bob Phillips" wrote:

That is because Sloth's Rounds the subtracted dates, so if it is 24.6 say,
it rounds it up to 25, and then the .6 is used to calculate the hours. So, I
think he is wrong

--

HTH

RP
(remove nothere from the email address if mailing direct)


"marie" wrote in message
...
Hi Bob, I compared the result using your formula with the one provided by
"Sloth" and it appears that I am off by a day using yours. 'Not sure why.


"Bob Phillips" wrote:

How about

=INT(A21-NOW())&" days, "&TEXT(MOD(A21-NOW(),1)," hh:mm:ss")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"marie" wrote in message
...
Hello experts,
What formula should I use to show # days left, including time (in

hrs,
mins and sec's) from current date/time to a target end date/time?
For instance:

Start Date: Now()
End Date: December 31, 2005 5:00:00 PM

Your help is greatly appreciated.
Marie









All times are GMT +1. The time now is 10:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com