Return cell value if date is between two dates

Lforeman

New Member
Joined
Mar 18, 2019
Messages
2
A
B
C
D
E
1
Job Number
Date
Amount
Week 1
Week 2
2
02/03/2019
09/03/2019
3
08/03/2019
15/03/2019

4
201
05/03/2019
3182.20
5
201
05/03/2019
2137.80
6
201
07/03/2019
4349.80
7
201
08/03/2019
2362.80

8
201
11/03/2019
2623.60

<tbody>
</tbody>

I need help please in determining the correct formula for returning a value if date is between two date.

The above data is an example of what I am working with.

In cell D4, I want to show the value of C4 if B4 falls within (and including) the date range of week 1 (D2 and D3).

All attempts have resulted in a false result.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
Hi Lforeman,

If you want sum between dates & per Job number maybe you could use this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #FFFFFF;background-color: #4472C4;;">Job Number</td><td style="color: #FFFFFF;background-color: #4472C4;;">Date</td><td style="color: #FFFFFF;background-color: #4472C4;;">Amount</td><td style="text-align: right;color: #FFFFFF;;"></td><td style="color: #FFFFFF;background-color: #ED7D31;;">Job Number</td><td style="text-align: right;color: #FFFFFF;background-color: #ED7D31;;">Week 1</td><td style="text-align: right;color: #FFFFFF;background-color: #ED7D31;;">Week 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Transactions</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">02-03-19</td><td style="text-align: right;;">09-03-19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">08-03-19</td><td style="text-align: right;;">15-03-19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">201</td><td style="text-align: right;;">05-03-19</td><td style="text-align: right;;">$3,182.20</td><td style="text-align: right;;"></td><td style=";">201</td><td style="text-align: right;;">$12,032.60</td><td style="text-align: right;;">$2,623.60</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">201</td><td style="text-align: right;;">05-03-19</td><td style="text-align: right;;">$2,137.80</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">201</td><td style="text-align: right;;">07-03-19</td><td style="text-align: right;;">$4,349.80</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">201</td><td style="text-align: right;;">08-03-19</td><td style="text-align: right;;">$2,362.80</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">201</td><td style="text-align: right;;">11-03-19</td><td style="text-align: right;;">$2,623.60</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F4</th><td style="text-align:left">=SUMIFS(<font color="Blue">$C$4:$C$8,$B$4:$B$8,">="&F$2,$B$4:$B$8,"<="&F$3,$A$4:$A$8,$E4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G4</th><td style="text-align:left">=SUMIFS(<font color="Blue">$C$4:$C$8,$B$4:$B$8,">="&G$2,$B$4:$B$8,"<="&G$3,$A$4:$A$8,$E4</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,778
maybe:
Code:
D4: =IF(AND($B4>=D$2,$B4<=D$3),$C4,0)
 
Last edited:

Lforeman

New Member
Joined
Mar 18, 2019
Messages
2
Thank you for your responses.

I have worked out that it is a report formatting problem not a formula problem. Sandy666 your formula worked, thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,407
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top