Counting zero billing days in Excel until current date

G

gotido

New Member
Joined
Dec 13, 2017
Messages
41
Office Version
  1. 365
Platform
  1. MacOS
I have a table with the following data:

In cells C2:AG2, I have the days of the month in the format dd/mm/yyyy.
e.g., 01/02/2024 02/02/2024 03/02/2024 04/02/2024 05/02/2024 06/02/2024 07/02/2024 08/02/2024 09/02/2024

In cells C3:AG3, I have the daily billing results:
€1,786.60 €117.89 €18.29 €0.00 €426.85 €567.08 €229.68 €303.26 €288.62

In cell AI1, I have the date =TODAY(), which provides the current date.
In cell AI3, I want the formula to give the number of days with zero billing, from the beginning of the month until the date indicated in AI1.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
try this:
Book3
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1
22024-02-012024-02-022024-02-032024-02-042024-02-052024-02-062024-02-072024-02-082024-02-092024-02-102024-02-112024-02-122024-02-132024-02-142024-02-152024-02-162024-02-172024-02-182024-02-192024-02-202024-02-212024-02-222024-02-232024-02-242024-02-252024-02-262024-02-272024-02-282024-02-292024-03-012024-03-022024-02-21
3145.29153.21144.9900000101.49116.7154.1125.92120.88128.48153.58118.73161.58113.36169.29147.07128.36113.45164.5106.2105.64126.1176.83111.87104.34106.98101.735
Sheet1
Cell Formulas
RangeFormula
AI2AI2=TODAY()
AI3AI3=COUNTIFS($C$3:$AG$3,0,$C$2:$AG$2,"<="&AI2)
 
Upvote 0
it worked but i need a plus because it count the non Workdays, like saturday and sunday, where the billing is always 0.
 
Upvote 0
you didn't say that in your original question. :)

Here:

Book3
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1
2Thu 2024/2/1Fri 2024/2/2Sat 2024/2/3Sun 2024/2/4Mon 2024/2/5Tue 2024/2/6Wed 2024/2/7Thu 2024/2/8Fri 2024/2/9Sat 2024/2/10Sun 2024/2/11Mon 2024/2/12Tue 2024/2/13Wed 2024/2/14Thu 2024/2/15Fri 2024/2/16Sat 2024/2/17Sun 2024/2/18Mon 2024/2/19Tue 2024/2/20Wed 2024/2/21Thu 2024/2/22Fri 2024/2/23Sat 2024/2/24Sun 2024/2/25Mon 2024/2/26Tue 2024/2/27Wed 2024/2/28Thu 2024/2/29Fri 2024/3/1Sat 2024/3/22024-02-21
3145.29153.21144.9900000101.49116.7154.1125.92120.88128.48153.58118.73161.58113.36169.29147.07128.36113.45164.5106.2105.64126.1176.83111.87104.34106.98101.734
4
Sheet1
Cell Formulas
RangeFormula
AI2AI2=TODAY()
AI3AI3=SUM(IFERROR(1/(WEEKDAY(C2:AG2,2)<6)/(C3:AG3=0)/(C2:AG2<=AI2),""))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Solved
Count the number of days between two dates that are in 2024
2
Replies
15
Views
288
Fluff
Fluff
M
  • Solved
How to count certain month from a column
Replies
3
Views
113
Joe4
Joe4
A
  • Question
Insert new column(DATE) up to finish month and move to next sheet
Replies
0
Views
126
Amer Omar
A
P
  • Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
211
POLACOLOCO
P
B
  • Question
Weekly dates with split across weeks with two months
Replies
1
Views
157
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,073
Messages
6,122,970
Members
449,095
Latest member
Mr Hughes

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