Conditional Function not Producing Results

[josedorkis]

Hello,
Do you guys see a problem with this function:
=IF(AND(G15=4/25/2020,A16="S&B"),G16,"")
It is not producing the value of G16 even though the conditions are met and G16 has a value of 24
Thanks

[etaf]

4 divided by 25 divided by 2020
try
=IF(AND(G15=datevalue("4/25/2020"),A16="S&B"),G16,"")

[josedorkis]

Thank you, but it produced this value:
1/24/1900
And that is not what is supposed to show. It is supposed to show the value of G16 which is currently 24

[josedorkis]

the 4/25/2020 is a date

[etaf]

load a sample file
I'm in UK so dates will be different

G15 is a DATE ? and NOT text and it is 25th April 2020
Format G15 -to general - does it stay as a date or change to a number

[MrShorty]

it produced this value:
1/24/1900 And that is not what is supposed to show. It is supposed to show the value of G16 which is currently 24

1/24/1900 is how Excel will display the number 24 as a date. It looks like Excel is returning 24 just as you expect, but the cell is formatted as date. Change the cell's number formatting to something more appropriate.

[josedorkis]

Done by changing the format of the cell. Thanks guys

[josedorkis]

Can I do this having the conditions in one sheet and the target cell on another sheet of the same workbook?

[etaf]

you are welcome

[etaf]

yes
use sheetname!
or
'sheet name'!

then cell - whats on a different sheet

[josedorkis]

Thank you.
Another question:
Can I formulate G4 to contain the next calendar day off of the date range mentioned in the period row of this screenshot?
Attachment 676061

[etaf]

cant see the attachment & dont understand the question, sorry

[josedorkis]

Yes, the attachment is crucial to understand the question.
Here is a word attachment.Doc1.docx

The question is if I could formulate G4 to obtain the date after the range of row 3.
Thanks again,

[etaf]

why a word doc and not the spreadsheet excel itself ?????
I dont have to re-create then
Also what results you expect

[josedorkis]

I see. Here is the excel file.
Attachment 676161
I have not done anything about this particular question. I was just wondering if I could automatically fill G4 with the date after the range stated on row 3. Right now, I have manually assigned G4 that date that I want, but I would like to be automatically off of the date range mentioned.
Thanks

[etaf]

the attachment does not work -
See the instructions in the yellow banner top of screen

you need to explain a bit more

g4 has a date in of 25th
next in period, what period - why is this 25th based on the info in the sheet?

[MrShorty]

Short but useless answer -- yes, you can formulate G4 to be the date after the end of a date range.

B3 contains a text string representing the date range. The process would then be:
1) Extract the ending date string (using RIGHT() and maybe a FIND()/SEARCH())
2) Convert the resulting text string to a date serial number (another DATEVALUE() like etaf used in post #2)
3) Then add 1 to it.

I hate dealing with text strings like this. Unless I was told that life, liberty, or national security required us to use only B3 for the date range, I would:
1) Enter the starting date (as a date) in B3
2) Enter the ending date (as a date) in C3
3) G4 then becomes a a simple =C3+1

[josedorkis]

That's a great solution. Thanks.

[josedorkis]

Can you also suggest a way of keeping the values of the "graph" sheet based on date value given on the "Therapist" sheet. Right now, it is assigning the correct value to each cell given the conditions of the formulas, but I would like to keep values given prior to the date specified.
For example, in the attachment, you only see a value under 4/11/2020 of the graph sheet as formulated, which is correct, but I would like to maintain the values of the 4/4, 3/28, etc. etc which I had entered in the therapist sheet the weeks prior.
Thanks,
Jose

[josedorkis]

Attachment 676753
Here is the attachment, I am sorry

[MrShorty]

Attachment failed to attach correctly.

[josedorkis]

Good morning,
Did you try to attach a file? Your last thread is showing a failure in that regard...
Thanks again,
Jose

[MrShorty]

Nope I did not try to attach a file, I was observing that I could not view your attachment in post 20.

[josedorkis]

Attachment 676910
Here is the attachment I intended to show you. Please let me know if you are able to see it this time

[MrShorty]

Still says invalid attachment.

[AliGW]

Jose - follow the instructions in the yellow box at the top of the page to upload your workbook.

[josedorkis]

Thank you.
Please try it now.
Thanks again

[MrShorty]

I think it will depend on exactly how you are intending to use this workbook. I could see suggesting anything from a manual copy -> paste as values kind of thing (if interact with this sheet rarely) to something that involves redesigning the workbook into a better data base type format (if you need to interact with this sheet frequently). The advantage of copy/paste as values is that you retain all of the "thinking" in your head and the spreadsheet doesn't need to do any of the thinking. The advantage of a good database is that you can then access pivot tables, filters, and many other useful utilities for summarizing and charting the data. It all depends on how you are intending to use this workbook.

[josedorkis]

There will be constant interaction so a better database must be a better solution...

[MrShorty]

I don't do database work, so I don't think I can be much help to you. Some of our regulars are pretty good with databases in Excel, though, and should be able to help design a workable database. Until then, some basic ideas to review:
https://www.excelforum.com/access-ta...ml#post5315969
https://www.excelforum.com/tips-and-...for-excel.html
a couple of links to external pages: https://www.excelforum.com/excel-new...ml#post4906705

I also expect those who come to help will want to know more about how you want to interact with the workbook, what data you need to store, what you will need to do with the data, etc.

[MrShorty]

deleted -- wrong thread.

[alansidman]

If you are considering a database, I would suggest using MS Access. Here is a white paper on how to design and normalize your data.

[oeldere]

@josedorkis

29 post just to make clear what you want to achieve.

I hope you follow the forumrules from now on.


First advice, don't work with merged cells you get in trouble with it sooner or later.


Add all data in a verticaly way, like I show you in the sheet Oeldere.

In this case I used VBA code for that.

After that you can use a pivot table to analyse the data.

After that it is also possible to create a graph.

See the attached file.