Home » Forums » Open Source Learning Center » Question and Answer

looking for IF Date range formula

Submitted by Loki on 2 December, 2012 - 05:43

ive got an xls i use for project tracking and invoiceing.
looking to get it to compare an entered date to a date range
and if that is true then add #'s.

any help guidance?

Loki

AttachmentSize
sample.xlsx12.95 KB
‹ Macro moving data basd on date Calculating day differences between two cells ›
»
Almir's picture

Post your file here

Submitted by Almir on 2 December, 2012 - 18:59.

Please post your file here.

»

ok. so in the example file

Submitted by Loki on 4 December, 2012 - 01:12.

ok. so in the example file attached, i want to be able to easily add up my billable hours for a 2 week date range.

so on the total line i'm looking to have the program look at the date column (I) and if it falls within a date range, then add the numbers up from column G to get my billable hours for a 2 week timeframe.

thanx
mike

»

posting file

Submitted by Loki on 3 December, 2012 - 16:45.

would love to. how is that done?

»

Basic solution

Submitted by AndyLitch on 3 December, 2012 - 15:50.

Assuming the date in question is in range A1 something like:-
=IF(AND(A1<=TODAY()+30, A1>=TODAY()-30),"TRUE","FALSE") would do it

+/- 30 is your date range in the above function

»

thanx AndyLitch. i guess i

Submitted by Loki on 3 December, 2012 - 16:47.

thanx AndyLitch. i guess i should have posted my whole goal.
i've got a column for dates and a column for hours. looking to add up the hours within a date range to get my billable hours.
function would look at date column first, if date between xx/xx/xx - xx/xx/xx, then add hour column #.

loki

»

=IF(AND(A1<=TODAY()+30,

Submitted by AndyLitch on 3 December, 2012 - 16:55.

=IF(AND(A1<=TODAY()+30, A1>=TODAY()-30),Sum(C1:E1),"")

The above will sum columns C to E (hours) if the date falls within the range which in this case is +/-30 days

Alternatively if you have a table of dates v's hours and you want to sum the column then you need a sumif function but I'm not clear exactly what you need.

If you want to attach a demo file, you will need to start a new thread and select the attach file button ...

»

how do we attach files here?

Submitted by Loki on 3 December, 2012 - 17:16.

how do we attach files here?

»

Attach file

Submitted by manny on 3 December, 2012 - 20:03.

Hi,

You can attach a file here.

To attach a file:

 1) Log In;

 2) Go to your forum topic -> Edit tab;

 3) Into File attachments section attach your file (browse your file and then press Attach button);

 4) Save your topic.

 

Best regards.

»

You can attach files to new

Submitted by AndyLitch on 3 December, 2012 - 17:17.

You can attach files to new threads only...

»