Calculate days between date or directly days comparison

V

vishu

Board Regular
Joined
Oct 26, 2011
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Hello friends
Need to compare last months days and current months days total sell.
Column A and B is Pervious months date and Per day sell.
Column D and E is Current months date and Per day sell.
between G2 n H2 or I2 will mention days number (like either in G2 and H2 from 1st date to 5th date or 7th to 15th date any between days or in I2 directly 5 day so) and in B2 & E2 want formula where shows those days total amount. if needed please use helper
I might not explain properly if any doubt please let me know and please please help


TotalTotalDateDateDays
15
DATEPer DayDATEPer Day
1-Apr-24395861-May-2438107
2-Apr-24592192-May-2463566
3-Apr-24474113-May-2466453
4-Apr-24550714-May-2460420
5-Apr-24771655-May-2467651
6-Apr-24685766-May-2430871
7-Apr-241227877-May-2452647
8-Apr-24670398-May-2468460
9-Apr-24457729-May-2466714
10-Apr-248252410-May-2444808
11-Apr-246155411-May-24100216
12-Apr-247492012-May-2458495
13-Apr-2410584313-May-2455261
14-Apr-245329814-May-2455117
15-Apr-248933715-May-2478798
16-Apr-2458752
17-Apr-2470871
18-Apr-2486116
19-Apr-2474556
20-Apr-2457534
21-Apr-2486412
22-Apr-2463105
23-Apr-2466125
24-Apr-2472361
25-Apr-2453201
26-Apr-2480105
27-Apr-2489955
28-Apr-2491477
29-Apr-2472839
30-Apr-2456829
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
I don't really know for sure that this is what you want. And I don't understand what cell I2 is for.
But, what does this do for you:

Book1
ABCDEFGHI
1TotalTotalDateDateDays
2278,452296,19715
3DATEPer DayDATEPer Day
42024-04-0139,5862024-05-0138,107
52024-04-0259,2192024-05-0263,566
62024-04-0347,4112024-05-0366,453
72024-04-0455,0712024-05-0460,420
82024-04-0577,1652024-05-0567,651
92024-04-0668,5762024-05-0630,871
102024-04-07122,7872024-05-0752,647
112024-04-0867,0392024-05-0868,460
122024-04-0945,7722024-05-0966,714
132024-04-1082,5242024-05-1044,808
142024-04-1161,5542024-05-11100,216
152024-04-1274,9202024-05-1258,495
162024-04-13105,8432024-05-1355,261
172024-04-1453,2982024-05-1455,117
182024-04-1589,3372024-05-1578,798
192024-04-1658,752
202024-04-1770,871
212024-04-1886,116
222024-04-1974,556
232024-04-2057,534
242024-04-2186,412
252024-04-2263,105
262024-04-2366,125
272024-04-2472,361
282024-04-2553,201
292024-04-2680,105
302024-04-2789,955
312024-04-2891,477
322024-04-2972,839
332024-04-3056,829
34
Sheet1
Cell Formulas
RangeFormula
B2B2=SUMPRODUCT((($B$4:$B$33)*(DAY($A$4:$A$33)>=$G$2)*(DAY($A$4:$A$33)<=$H$2)))
E2E2=SUMPRODUCT((($E$4:$E$18)*(DAY($D$4:$D$18)>=$G$2)*(DAY($D$4:$D$18)<=$H$2)))
 
Upvote 0
awoohaw said:
I don't really know for sure that this is what you want. And I don't understand what cell I2 is for.
But, what does this do for you:

Book1
ABCDEFGHI
1TotalTotalDateDateDays
2278,452296,19715
3DATEPer DayDATEPer Day
42024-04-0139,5862024-05-0138,107
52024-04-0259,2192024-05-0263,566
62024-04-0347,4112024-05-0366,453
72024-04-0455,0712024-05-0460,420
82024-04-0577,1652024-05-0567,651
92024-04-0668,5762024-05-0630,871
102024-04-07122,7872024-05-0752,647
112024-04-0867,0392024-05-0868,460
122024-04-0945,7722024-05-0966,714
132024-04-1082,5242024-05-1044,808
142024-04-1161,5542024-05-11100,216
152024-04-1274,9202024-05-1258,495
162024-04-13105,8432024-05-1355,261
172024-04-1453,2982024-05-1455,117
182024-04-1589,3372024-05-1578,798
192024-04-1658,752
202024-04-1770,871
212024-04-1886,116
222024-04-1974,556
232024-04-2057,534
242024-04-2186,412
252024-04-2263,105
262024-04-2366,125
272024-04-2472,361
282024-04-2553,201
292024-04-2680,105
302024-04-2789,955
312024-04-2891,477
322024-04-2972,839
332024-04-3056,829
34
Sheet1
Cell Formulas
RangeFormula
B2B2=SUMPRODUCT((($B$4:$B$33)*(DAY($A$4:$A$33)>=$G$2)*(DAY($A$4:$A$33)<=$H$2)))
E2E2=SUMPRODUCT((($E$4:$E$18)*(DAY($D$4:$D$18)>=$G$2)*(DAY($D$4:$D$18)<=$H$2)))
Click to expand...
Ohh wow great sir, fantabulous superrrrr :) 👍
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
K
  • Solved
Calculate Total Expense With Condition
2
Replies
10
Views
511
Cubist
Cubist
H
  • Question Question
The number of days per month that fall between two dates
Replies
2
Views
610
helpExcelme
H
E
  • Question Question
Average a range between 2 dates
Replies
3
Views
545
Fluff
Fluff
J
  • Question Question
Calculating the number of 15 min increments between two dates and weeks
Replies
2
Views
621
SpillerBD
S

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top