Time Question

Grimbo2k

Board Regular
Joined
Sep 28, 2004
Messages
74
Hi all
Hopefully this is a simple question

I have a date in the future and I need a formula which tells me how many days/hours/mins/secs remain based on current time/date

ie

10 days, 4 hours, 3 mins, 20 seconds remaining

Had a quick play with it and got stuck !

Any thoughts much appreciated

Regards
Grimbo
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

A1 = 28/09/2006 12:00

B1 = 29/09/2006 12:30

D1 = =(B1-A1)*1440*60

E1 (days) = =ROUNDDOWN((D1/86400),0)

F1 (hours) = =ROUNDDOWN((D1-(E1*86400))/3600,0)

G1 (mins) = =ROUNDDOWN((D1-(E1*86400)-(F1*3600))/60,0)

H1 (secs) = =ROUNDDOWN((D1-(E1*86400)-(F1*3600)-(G1*60))/60,0)


you could combine these into one cell, with "days", "mins" etc added - but its nearly lunch.

this is the one cell formula

=ROUNDDOWN(((B1-A1)*1440*60/86400),0)& " days "&ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400))/3600,0)&" hours "& ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400)-(ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400))/3600,0)*3600))/60,0)&" mins "&ROUND((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400)-(ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400))/3600,0)*3600)-(ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400)-(ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400))/3600,0)*3600))/60,0)*60),0) & "secs"

I'm sure thes is an easier way though (is Aladin about) or anyone else?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
With your future date in A1

=INT(A1-NOW())&" days,"&TEXT(A1-NOW(),"h \hour\s, m \mi\n\s, s")&" seconds remaining"

although it won't update automatically, only when worksheet is recalculated (which you can initiate by hitting F9)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,798
Messages
5,544,343
Members
410,604
Latest member
jeffamore
Top