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

=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

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" <(E-Mail Removed)> wrote in message
news7A0EAAD-43F3-429C-838A-(E-Mail Removed)...
> 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
>

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

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" <(E-Mail Removed)> wrote in message
> news7A0EAAD-43F3-429C-838A-(E-Mail Removed)...
> > 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
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:196235A1-2B5A-4AD1-B412-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news7A0EAAD-43F3-429C-838A-(E-Mail Removed)...
> > > 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
> > >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:u$%2337sr%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:196235A1-2B5A-4AD1-B412-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news7A0EAAD-43F3-429C-838A-(E-Mail Removed)...
> > > > 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
> > > >
> > >
> > >
> > >
>
>

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" <(E-Mail Removed)> wrote in message
> news:196235A1-2B5A-4AD1-B412-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news7A0EAAD-43F3-429C-838A-(E-Mail Removed)...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>