Trying to add the current day (in number) into a formula and failing miserabily

[josepharari]

Hello all,

I am a total newbie in Excel but I love it and I usually am able to do simple things just by trying until I get it, but this time I am completely stumped as nothing I try works. I will explain why I want to do what I am asking, but if you are not interested feel free to jump to the part with the TL;DR

Basically, I keep a very simple table that logs the hours I call. I work in a call center, and the only information I get is the average time per-call, the total amount of answered calls I have made in a month and the total amount of calls done in the current day (answered and unanswered). I don't get my total time called per day nor my answered calls per day.

What I did was set up several cells to do the calculation for me. First I did a table with every single day and when I get to the office the first thing I do is update manually the number of total answered calls I had at the beginning of the day. I have another cell where I can write the total amount of calls I have made during the day (this updates automatically as I call), I rest the first number from the second number and I get the total answered calls I have made so far that day. I then multiply that number by the average time per-call, and I get the total time I have called during that day.

TL;DR What I am trying to do is set up a function that updates daily with the current day, and then adds that number automatically to the cell number. For example, today is the 30th of June, I get to the office today and I see I have 500 answered calls so I enter "500" into the table next to the "30" which is today. However I then have to change the formula that rests the cell with the total amount of calls (aka this cell) from the cell with the total amount of calls done in the current day manually, from say =A1-B29 to =A1-B30. I have to do that every single day and I was wondering if I somehow could make a formula that would change this automatically. I know =DAY(TODAY()) returns the current date, but so far I have been absolutely unable to insert this into the =A1-BX formula, to something like =A1-B(=DAY(TODAY())).

Any help would be greatly appreciated. Thank you.

 

[DHayes]

Afternoon,
I hope this what you are looking for. it was quite a lot of reading.
=CONCATENATE(SUM(A1-B1)," ",DAY(TODAY()))

 

[josepharari]

Afternoon,
I hope this what you are looking for. it was quite a lot of reading.
=CONCATENATE(SUM(A1-B1)," ",DAY(TODAY()))

Hi, thank you for the quick reply

I already tried this and I get an error, what I want is to replace the "1" in "B" to whatever DAY(TODAY)) returns. So for example, today when I open the excel file the formula will be =A1-B30 automatically without having to enter that 30 manually. If I open the formula tomorrow it will be =A1-B1 because it will be the first of july. And so on.

 

[DHayes]

You will have to get someone good in VBA to assist you.

 

[josepharari]

That's what I feared, this is why I have been failing for so long, because I am sure the answer will be something extremely complicated even though doing something like this with Python would be very simple.

I hope somebody more knowledgeable is able to help. Thank you for trying, I really appreciate it though.

 

[josepharari]

bump

 

[josepharari]

last bump, maybe somebody will see it over the weekend

 

[Osvaldo Palmeiro]

Hello, Joseph.
See if this is something that could help you.

=A1-INDIRECT("B"&DAY(TODAY()))

 

[josepharari]

Hello, Joseph.
See if this is something that could help you.

=A1-INDIRECT("B"&DAY(TODAY()))

Why yes, yes it does! Been searching for a solution for I don't know how long, thank you!!!