Working with Time and Date

**soma104** · 02-12-2010, 10:21 AM

Hi,

I have to perform several different calculations using time and date, and am more confused after searching through forums and help files.

On my excel worksheet there is a cell that will feature start date/time. I need to calculate start time + 24 hours, 48 hours and 72 hours, and be able to display the date and times in another cell.

Once I'm done with those calculations I will need to go back 7 days from current date/time, and add up the total amout of time in days and hours between each start/end times withing those 7 days.

I'm not sure if it would be easier to do this with formulas on the worksheet, or in vba

Soma

**daddylonglegs** · 02-12-2010, 10:27 AM

I'm not clear on the second requirement but adding hours to a date is quite simple with formulas. If you have a date/time in A1 you can add 48 hours like this

=A1+"48:00"

but if you are always adding multiples of 24 hours it's simpler to do the same like this

=A1+2

those two both give the same result because in Excel 1= 1 day = 24:00

**soma104** · 02-12-2010, 10:41 AM

Thanks Daddy,

That's so much simpler that what I've been seeing online.

My second requirement is a little more complicated. In a 7 days period (168 hours going backwards from current date/time) I need to find entries where the start date falls within the past 168 hours.

For example

Feb 4, 2010 - start time, 08:00 - end time, 14:00
Feb 7, 2010 - start time, 13:00 - end time, 17:00
Feb 10, 2010 - start time, 09:30 - end time, 16:45

Going back 168 hours from now, the first entry on Feb 4 falls outside the period I'm looking at and can ignore. What I need is to be able to add 4 hours on Feb 7th to 7:45 hours on Feb 10. Therefore, in the past 7 days (168 hours) there is a total time of 11:45 hours.

Hopefully I've explained that well

Soma

**ChemistB** · 02-12-2010, 11:21 AM

Are your dates, beginning times and ending times in 3 different columns? If so,

=SUMPRODUCT(--(NOW()-($A$2:$A$500)<7),($C$2:$C$500-$B$2:$B$500))

Format the cell >number >custom [hh]:mm Does that work for you?

**soma104** · 02-12-2010, 11:50 AM

Thanks Chemist,

It wasn't exactly what I was looking for as my data is in rows, but it all helps.

I think what I may do is have another colum with all the time elapsed for each row and then write a long if statement to select which ones to use.

I do have another question though.....

I've used the following formula to determine the elapsed time between start and end date

=24*(IF(B3>G3,G3+1-B3,G3-B3))

The entry that I'm working on is 3 days, 5 hours. The result I'm getting from the formula is 53 hours. How can I get it to show "3 days, 5 hours"

Soma

**shg** · 02-12-2010, 12:52 PM

That formula is intended for applications when the interval may be overnight, but less than 24 hours (e.g., timecards).

For intervals up to 31 days, just =G3-B3 and format as d "days" h "hours"