Excel Formula?

[rctempire]

Hello all,

Trying to do a simple task, that seems to be quite hard to find a resolve.

Basically i want to figure out how to compare two times and dates and display them as a Day,Hours,Mins.

So table goes as follows.

Name:------------------DATE-------------------Today Date
Bob -------------------01/09/2008 12:01---------23/01/09 11:41

( i have used =NOW() for the time now)

Now how do i display this as in another cell

DD : HH : MM
Basically I stuck and have been looking around for ages.

Anyone help?

 

[Sym0n]

Only way I can quickly think of would be to seperate your cells so that date and time have their own individually. Then use something like =datedif(A1,B1,"d") in cell E1 and then =text(C1-D1,"hh:mm") in F1. Then finally use =concatenate(E1,":",F1) to output to G1.

Long winded and dirty I know but excel isn't really my strong point.

 

[Sym0n]

I tested it and it does work...as long as the current time isn't later than the original time lol. So change =text(C1-D1,"hh:mm") to =text(C1-D1+IF(C1<D1,1),"[hh]:mm") quick test and it seems to work now, google to the rescue.

As I forgot to add this earlier:
A1 - Original date
B1 - =today()
C1 - Original time
D1 - Current time (can't find a way to actually enter just the time)
E1 - =datedif(A1,B1,"d")
F1 - =TEXT(C1-D1+IF(C1<D1,1),"[hh]:mm")
G1 - =CONCATENATE(E1,":",F1)

It's by no means perfect but I hope it helps along the way to what you want.

Sy

 

[rctempire]

The problem lies with that say the date and time was say 8 days ago and the current day time is smaller than the original.

 

[Sym0n]

The problem lies with that say the date and time was say 8 days ago and the current day time is smaller than the original.

Not quite sure if you're telling me why the original formula (=text(C1-D1,"hh:mm")) failed, which I'd already figured out whilst googling lol, or saying that you are going to get a current date which is earlier than the original date that you're wanting to work the difference out from? :S

Even google can't tell me how to build a time machine lol. :clown:

 

[iain187]

what exactly are you trying to display in another cell? I am not sure..

 

[MarkCup]

I think you're making this far too complicated!

Have no time now...but if I'm right I've got a much simpler solution.

I'll post something up later once I've tried it out.

 

[@Spoonie]

Removed, wrong..

 

[MarkCup]

Nope, can't make it work.

 

[rctempire]

Right simple terms.
I have to dates of "uptimes"
Date 1 - 18/01/2009 09:45
Date 2 - Today (24/01/2009 02:09)

I wish to calculate the difference in the date and display it in Days:Hours

That a bit easier? I was in a blur of VBcode trying to work it out.