Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 6th 05, 09:30 PM posted to microsoft.public.excel.worksheet.functions
marie
 
Posts: n/a
Default 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


  #2   Report Post  
Old December 6th 05, 09:51 PM posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default 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   Report Post  
Old December 6th 05, 10:03 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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
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

Your help is greatly appreciated.
Marie



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

Your help is greatly appreciated.
Marie






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

Your help is greatly appreciated.
Marie






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

Your help is greatly appreciated.
Marie








  #8   Report Post  
Old December 7th 05, 02:36 PM posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default 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
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

Your help is greatly appreciated.
Marie









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating time and pay in excel wobbleman Excel Discussion (Misc queries) 1 December 6th 05 10:59 PM
calculating date/time relaing Excel Discussion (Misc queries) 4 December 5th 05 03:56 PM
How do place a column of date/time information into a calendar for Linuxster Excel Discussion (Misc queries) 0 December 3rd 05 12:56 AM
Calculating Time Paul Cooke Excel Discussion (Misc queries) 4 December 1st 05 07:51 PM
Working days left in the month compared to previous months qwopzxnm Excel Worksheet Functions 8 October 24th 05 08:00 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017