Remember Me?

#1
December 6th 05, 09:30 PM posted to microsoft.public.excel.worksheet.functions
 marie Posts: n/a
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

Marie

#2
December 6th 05, 09:51 PM posted to microsoft.public.excel.worksheet.functions
 Sloth Posts: n/a
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
#3
December 6th 05, 10:03 PM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a
Calculating days & time left from start date/time to end date/time

=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
news
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

Marie

#4
December 6th 05, 10:42 PM posted to microsoft.public.excel.worksheet.functions
 marie Posts: n/a
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

#5
December 6th 05, 10:46 PM posted to microsoft.public.excel.worksheet.functions
 marie Posts: n/a
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:

=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
news
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

Marie

#6
December 6th 05, 11:17 PM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a
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:

=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
news
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

Marie

#7
December 6th 05, 11:30 PM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a
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:

=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
news 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

Marie

#8
December 7th 05, 02:36 PM posted to microsoft.public.excel.worksheet.functions
 Sloth Posts: n/a
Calculating days & time left from start date/time to end date/

"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:

=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
news 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

Marie

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post wobbleman Excel Discussion (Misc queries) 1 December 6th 05 10:59 PM relaing Excel Discussion (Misc queries) 4 December 5th 05 03:56 PM Linuxster Excel Discussion (Misc queries) 0 December 3rd 05 12:56 AM Paul Cooke Excel Discussion (Misc queries) 4 December 1st 05 07:51 PM qwopzxnm Excel Worksheet Functions 8 October 24th 05 08:00 PM

All times are GMT +1. The time now is 12:20 AM.