• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

An =IF Date question

T

thpoulos

New Member
Hello

I will like some help with a report that I am producing

I have A field (a3) With =Today (), (named date).

I Have a Field (b1) with month to date value named MD updated manually every day.

And I have date fields from (a12:a33) the beginning of the month till the end,

What I need is a formula that will look the date on (a3) find the corresponding date on (a12:a33) and put the value from Md to the (b12:b33) fields, but only for the today date and leaving the previous date as they are.


I hope I am making sense.


Thank you in advance for any help.
 
Hi thpoulos,


Firstly welcome to this forum.


Would request you to please read the three green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on). We in Chandoo.org prefer people to upload the file so other can have better visualization of your data in order to have a better understanding of your requirement. For doing this further, please see the link below:


http://chandoo.org/forums/topic/posting-a-sample-workbook


Now, regarding your query, if I understand it correctly, then please see the file I have uploaded with solution at the below link:


https://hotfile.com/dl/164201023/abdecea/An_IF_Date_question.xlsx.html


In case I fail to understand your requirement, please let us know. We will be happy to help you..


Regards,

Kaushik
 
Hi, thpoulos!


I agree with kaushik03 about posting a sample file, but as I understood your question in a slightly different manner, here's my blind shoot:

https://dl.dropbox.com/u/60558749/An%20%3DIF%20Date%20question%20%28for%20thpoulos%20at%20chandoo.org%29.xlsx


If none of both answers achieves your goal, please elaborate it a bit more with manual examples or provide us a file to get our hands dirty on it.


Regards!
 
Hello

And thank you for your prompt answer to my little problem,

Here is a sample file to play with,

I need the formula to file the value of the day daily on the month list.


https://docs.google.com/open?id=0Bx4FkeIop9stMEVxS2NmSHAyNnc
 
Hi, thpoulos!


Here's a link to the updated sample file:

https://dl.dropbox.com/u/60558749/An%20%3DIF%20Date%20question%20-%20Copy%20of%20Figures%20Mansfield%20July%202012%20%28for%20thpoulos%20at%20chandoo.org%29.xlsm


I added the ranged name definition for MD that was missing, and created a new named range called MonthDates ranging from rows 12:39.


One doubt: you have 23/07/2012 (dd/mm) in A3, 591911 in B1, but cell B28 (where A28 is 24/07/2012) has the value of MD instead of B29... are they shifted by one or I missed something?


Just advise if any issue.


Regards!
 
Hi SirJB7


I am not sure what happened with the A3 date it should be =Today(),

I have fixed it now. Unless we are on a different time zone?

So what I need from all this is to input the value on B1 & and have excel to fill the Value from B1 to the range of rows B12:39 (next to the corresponding date on rows A12:39).

Also if you have any suggestions as how to better my report I will appreciate it.

Thank you for your help in advance.
 
Hi, thpoulos!

I don't know you, but I'm at GMT-3. Maybe that explains the shifting. And about your needs, I think that the uploaded file does such a thing. In order to reduce the natural days you'd need to test the workbook, I suggest you to manually change A3 value and input different MD values, so as to check if table is properly fulfilled.

Regards!
 
Hi, thpoulos,


I did not get your query at the first place and provide you a different answer..apologies for that.


But I see our SirJ(ames)B(ond)(00)7 (of excel) has solved it.Thank you SirJB7.


Regards,

Kaushik
 
Hi SirJB7


Well it works, I am really thankful of your help,

I was struggling with this for some time until I stumble to this site,

After looking at the work quality & examples on this site I feel very inadequate

But I will, when time permits me to get a better understanding of excel.

By the way do you have any suggestions as to how to make my report more attractive, (with maybe Charts, Pivot Tables, Dashboards)? Any suggestions are welcome,

I am on +10 time zone Brisbane Australia,

Thanks again for your help.
 
Hello SirJB7


How can I duplicate the function for the Number of Orders Tab.?

The manual input is on the D5,

Thank you.
 
Hi, thpoulos!


Glad you solved it. Thanks for your feedback and for your kind words too.


Making reports more attractive is mostly a design concept issue, and it's very tied to corporate policies or personal standards. My suggestion chains begins for understanding how to update and maintain the model, followed by a yes answer to the question "does someone from outer-business interprets what's in there?. Next step will add a yes to "are all recipients excited with it?". Check out these three, if you're done it'd be time to improve it technically, if not then just go by parts, step by step.


Welcome back whenever needed or wanted.


Regards!
 
@kaushik03

Hi!

You're welcome, even if I have to struggle trying to decipher what did you and the other guy tried to mean...

http://chandoo.org/forums/topic/range-lookup-1

Regards!
 
SirJB7,


Me, Pucha and Debraj all are from same religion and Bengali is our native language. I and Pucha just praised Debraj for the outstanding solution he provided in that post.


'Valo theko' (by Pucha)means "keep well"/"do well"/"wish you all the best" kind of...

"sotti oshadharon" (by me:))means "really outstanding/awesome/incredible" kind of

"Dhanyabaad Bondhu" (by Debraj) means "Thank you friend"


But I see in that post that you have also shared some words in Bengali.Do you also know bengali to some extent?


Regards,

Kaushik
 
Hello SirJB7


Thank you for your input & help, can I please impose on you for little longer,

on the second Tab Number of orders, the manual input is on row D5 but the updated file inputs the MD value from the first tab Invoice MD, on the date range, can you please change it to input the Value of Order Month to Date:(D5) on the date range?

Thank you.
 
Hi kaushik03


Thank you for your kindness and good words, a help and smile goes a long way,

May name is Theo, I am very pleased to have found this side,

Regards to all members.
 
@kaushik03

Hi!

Thanks for the information about you three -The Three Musketeers, from now on :), I guess- and for the translations too. I don't know a single word in Bengali so I had to google and draw upon pages like http://www.indiga.org/language/lenguas_cw.php to find out what did Debraj Roy and you said. I only succeded with his two words but I failed with both yours, so I aimed a shoot in the dark with mines, assuming you said something like as you translated now very kindly.

So answering your final question, I should say that my Bengali proficiency's extent is circumscribed to just dictionaries :(

It was just a way to say "hi, do you think I didn't catch up what you wrote?... well, you're right,... but read this:".

Regards!

PS: Sometime ago I build an Excel function reference and formula translator, maybe you find interesting giving a look at it, here's the link:

http://chandoo.org/forums/topic/excel-multilanguage-formula-translator-and-function-reference
 
Hi, thpoulos!

Updated, created a new named range OMD for updating second sheet. Download again the file from same previous link.

Regards!
 
Hi SirJB7


Here is another thought, can we have let’s say on row C38 on Tab Invoice MD & C38 on Tab Number of Orders,

A sum of average for the month? For example if let’s say the total of invoiced value is 500,00.00 for the active days so far & lets say is 19 working days so then the average so far is 26,315.78 per day and it will change to include every new active day till the end of month?

Can you help me with this?

Thank you in advance, you opened up my creative site but I don’t have the knowhow as of yet.
 
Respected SirJB7,


I got your multilingual translator excel and I was feeling really exciting when the spreadsheet opens up with the userform at the front.


But unfortunately I am not able to see the "decline" or accept" button on the form, and hence, can not close the form as well.


Can u please help me on this as I really want to understand and learn this tool in detail you have prepared?


One more QS sir: May I know your good name plz, if u don't mind?


Regards,

Kaushik
 
Hi, thpoulos!


A few questions, what's a "sum of the average for the month"? 500K divided by the 19 active days or by the 15 active days thru the date in sheet 'Invoice MD' cell A3? or anything else?

wouldn't the average of the month be the average of column C up to date in A3?


Regards!
 
Hi, kaushik03!


I actually missed it, I think I rested for a while until answering thpoulos while I was checking the file, and your comment got inserted previously. Sorry for that, and thank you to point it out again.


I think that there's an issue regarding the screen resolution. I was yet told about it, but only once so I delayed adjusting userform height. Give me a couple of days and I'll get back to you with the updated version. In the meanwhile perhaps you can use Tab to reach both buttons. This is the initial 4 steps sequence:

Decline command button

Accept command button

Download links text box

Contact information text box


Sorry for the inconvenience of assuming everybody had screen resolutions greater than 1080p, I actually missed net/note/books and displays with 1366x768 or alike.


Regards!


PS: my name is Pablo :)
 
You must log in or register to reply here.
Back
Top