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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

C
  • Question Question
Network days from a dropdown
Replies
4
Views
99
Asbestos_Jen
Asbestos_Jen
A
  • Question Question
How sort files contains specific word based on modified dates
Replies
0
Views
55
abdelfattah
A
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
77
Ali M
A
N
  • Question Question
Help w/ Excel Formula
Replies
4
Views
143
JGordon11
JGordon11
A
  • Solved
Date format changing ...
Replies
4
Views
196
adambc
A

Forum statistics

Threads
1,223,886
Messages
6,175,189
Members
452,616
Latest member
intern444

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