MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combining multiple columns of data - Subtracting date/time ranges


Posted by Kevin Mac on December 20, 2001 2:33 PM

If I wanted to combine a column of dates (column F) with a column of times (column G) into 1 column (column D) and then from that get an actual time elapsed between column D's date/time and another column with date/time format Column H, how would I go about that. I am fairly new to all this. I am also looking for a formula that will input current date/time TODAY() along with the NOW() time format in one column as (mm/dd/yyyy hh/mm)


Posted by Jacob on December 20, 2001 2:58 PM

Hi

To combine dates times you can just add or subtract them. 1 day is equal to 1 and 1 hour is 1/24 and 1 minute is 1/(24*60) etc so just add the times you want.

For the second part try this:

=today() & " " & now()

Hope this helps

Jacob

Posted by Kevin Mac on December 20, 2001 3:12 PM

This is the result i get from the =today/now formula with the column formatted as Date (03/01/01 01:30 PM) = result (37245 37245.7180333333)????

Posted by IML on December 20, 2001 3:15 PM

It redundant to have now() and today(). now() includes the date. just use =now() and format the cell however you would like.

good luck.

Posted by Kevin Mac - another satisfied newbie on MR EXCEL - THE WORLDS GREATEST EXCEL SUPPORT SITE on December 20, 2001 3:24 PM

Works like a champion - one last follow up questions though


Works like a champ now, thanks so much. One last follow up question. How do I get a difference in days/hours/minutes elapsed between date/time formats such as (12/31/01 12:31 AM 12/20/01 5:24 PM)


Posted by IML on December 20, 2001 3:30 PM

Re: Works like a champion - one last follow up questions though


assuming your larger number is always in B1, and smaller in a1, you could use
=b1-A1

format could be
d "days" h "hours" m "minutes"