Based on a Column datetime value auto calculate and populate a datetime range

**rajashanmuga** · 02-20-2010, 01:07 PM

Hi,

This is for Excel 2003. The requirement is:

First column (say A1) will have a date time in mm/dd/yyyy hh:mm:ss format (e.g. 02/02/2010 09:10:00),

Second Column (say A2) will have a frequency time in minutes e.g. 30 minutes

I need to sum A1 and A2 to populate A3, A4 value should be (A3+A2), A5 value should be (A4+A2) etc. this recurring calculation should be done for a 24 hours period starting from the date time value given in A1

How to achieve this in Macros? Advances thanks you for your help/inputs

**DonkeyOte** · 02-20-2010, 01:30 PM

Are you sure you want / need a Macro ?

The same could be achieved using formulae

A3: =IF(ROWS(A$3:A3)>1/$A$2,"",MAX($A1:$A2)+$A$2)
copied down to say A100 or as far as you think realistic (based on frequency interval)

once 24 hours have been accounted for the above will return Nulls

**rajashanmuga** · 02-22-2010, 02:06 PM

Hi,

Thank you for your reply. The formula is not working!

The result (date time range based on the input date time mm/dd/yyyy hh:mm and frequency hh:mm) will span across multiple columns but I wanted this execution to happen just not for one row (say 'A' row) but for multiple rows (say until 'K' row). Do you think a macro would be a better approach in this case? Any inputs?

**DonkeyOte** · 02-22-2010, 02:52 PM

First let's qualify what you want given you've used mixed terminology thus far.

When you say "First Column A1, Second Column A2" you mean what exactly - A1 & A2 (rows) or A1 & B1 (columns) ?

To avoid further confusion I'd suggest you post a file which outlines your desired results.

**rajashanmuga** · 02-22-2010, 04:10 PM

Hi

It is my bad. On the first row - column A1 will have a date time field (dd/mm/yy hh:mm), columns B1 will have the frequency in hh:mm format, from C1 until 24 hours (B1 value for time base) the columns have to be populated. This pattern/work should happen for multiple rows.

I am attaching a sample.xls for your reference. Any thoughts, please?

Based on a Column datetime value auto calculate and populate a datetime range

LinkBack

Thread Tools

Rate This Thread

Display

Based on a Column datetime value auto calculate and populate a datetime range

Re: Based on a Column datetime value auto calculate and populate a datetime range

Re: Based on a Column datetime value auto calculate and populate a datetime range

Re: Based on a Column datetime value auto calculate and populate a datetime range

Re: Based on a Column datetime value auto calculate and populate a datetime range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions