CODEWITHSUNDEEP
excelvbadatedatediff
B.Jones
B.Jones

Reputation: 33

Excel formula - Calculate Days, Hours, Minutes and seconds between 2 timestamps

I have a vba script which leaves a timestamp in cell A8 when run by the user like so:

ActiveSheet.Range("A8").Value = Format(Now(), "dd/mm/YYYY hh:mm:ss")

This displays the following in cell A8 (which is formatted as a date:

10/12/2016  9:15:10 AM

Next I want to be able to determine how many days, hours, minutes and seconds it has been since the last time the user ran the macro/vba code.

I am also capturing the current date/time by using =NOW() in cell B8.

Cell B8 is also formatted as a date and displays like so:

12/18/16 12:45

My formula should compare the timestamp in A8 with the current time and date, in cell B8.

=INT(A8-B8)&" days "&TEXT(A8-B8,"h"" hrs ""m"" mins """)

I have noted that the date formatting is not consistent here, and I don't know if this would matter?

For some reason I am not getting the desired result and I get this error with my formula:

Value!

Please can someone show me where I am going wrong?

In additions, if possible I do not want to display days, hours, minutes and seconds all at once like this:

0 days, 0 hours, 0 minutes, 0 seconds

What I actually want is if the time stamp is less than 60 seconds, to show something like:

55 seconds ago

Then something like:

20 minutes ago

then:

1 hour ago

then :

2 days ago

Thanks

Upvotes: 1

Views: 1947

Answers (1)

A.S.H
A.S.H

Reputation: 29332

First, it should be B8-A8, not the other way around, because B8 should be greater than A8. This change should make your initial formula work.

Second, your desired formula:

=IF(B8-A8>=1,INT(B8-A8)&" days ago",IF(HOUR(B8-A8)>=1,HOUR(B8-A8)&" hours ago",IF(MINUTE(B8-A8)>=1,MINUTE(B8-A8)&" minutes ago",IF(SECOND(B8-A8)>=1,SECOND(B8-A8)&" seconds ago", " just now"))))

p.s.: As per @YowE3K's comments, for this to work, your cells A8 and B8 should set to Date type, otherwise you should format them with the right format in the formulas. The first option should be easier.

Upvotes: 2

Related Questions