Excel booking system

[gideon]

Hello there,

I have the attached Excel file.
B:E list appointments I have.
I would like column K to show me on which dates I can book appointments, considering:
- The column K should say "No results" when I3:I5 and I10 or one of those 4 is empty (those fields are required, I7 and I9 are optional and should be ignored when empty).
- The appointment starts at 12:00 (or whatever is in I3).
- The appointment lasts 2 hours (or whatever is in I4).
- Between end and start of appointments, if applicable, I would like to have at least 01:00 hour (or whatever is in I5).
- If filled out, otherwise ignore: I would only like to see those dates where the postal code starts with the number or numbers (!) in I7.
- I would only like to see those dates between 10 (or whatever is in I9) and 365 (or whatever is in I10).
- Also, I'd like I4 to be a formatted as a number (of hours), not as an hour.

In this example, 21.04.2024, 28.04.2024, and 05.05.2024 should not be listed.

Thank you so much in advance!

[HansV]

Welcome to Eileen's Lounge!

Wow, that is a big request! I'll take a look tomorrow...

[gideon]

You're truly amazing! To be clear: I guess with "If filled out, otherwise ignore: I would only like to see those dates where the postal code starts with the number or numbers (!) in I7" I mean: the postal code of at least one other appointment at the same day starts with these numbers.

[HansV]

Oh dear, another complication...

[gideon]

My apologies. I guess you're getting what I'm attempting to accomplish here. I want Excel to list the dates that I can make an appointment, so that I don't need to check every date manually every time. And with the postal code, I'm trying to save driving time so that I can schedule appointments that are not too far from each other. If you think another approach would work better, feel free to let me know.

[HansV]

In the attached version, I created a helper column for each condition, and a column that combines all the conditions. This is used in the FILTER function.

Mappe1 (1).xlsx

[gideon]

This is just awesome!

In Germany, postal codes have 5 numbers and some of them start with a zero. I've altered the format of the E column, so that it now always has 5 digits, and of M7, so that it now always has at least 2 digits. In the attachment, when I put 1234 I meand all postal codes that start with 1234x. However, 21.04.2024 has TRUE, as well (01234). How can I correct this? (After all, 1234x is not the same as 01234.) After this, I believe we're done!

[gideon]

I do have one more question. Can O3 be set in such a way that it also lists those Sundays, where I don't have appointments yet at all, considering M9 (Minimum lead days) and M10 (Maximum lead days)?

[HansV]

Format both the Postal Code column and cell M7 as Text. and enter the values anew, including the leading zero.

In your example, ALL dates are Sundays. Do you only make appointments on Sundays?

[gideon]

I've done as you told me and it does function. However, the cells get an error message ("Numer Stored as Text"). I can change the "Error Checking Options" in my Excel, but how do I prevent that when this file is opened on another computer, it shows this error message?

And yes, in this particular example I only have appointments on Sundays. Thank you!

[HansV]

Here is a new version. I fear you'll have to live with the error triangles. A postal code such as 01234 IS a number stored as text...

Mappe1 (1) (4).xlsx

[gideon]

Thank you again, this is just great! When I have 12 in M10, 28.04.2024 doesn't and when I have 13 in M10, 28.04.2024 does appear in the O column. However, it should already appear when I have 8 in M10, as 8 days from today (20.04.2024) is 28.04.2024. When I change the first 7 in O3 into a 4, it works perfectly, but I'm not sure what this 7 or 4 does.

[gideon]

And with the 7 in the formula (so without the 4), when I have 1 in M9 and 13 in M10, I only get 21.04.2024, and when I have nothing in M9 and 13 in M10, I get 28.04.2024, as well. This seems odd.

[gideon]

One solution seems to be to replace the original (M10-M9+1; 7) in O3 with (M10+6; 7). But again, I'm not sure what these numbers are there for.

[gideon]

One more question I have. When I have 1123 in M7, it shows both the Sundays where I already have an appointment in a postal code starting with 1123 and the Sundays where I don't have appointments yet, at all. How can I let the O column only show those Sundays where I already have an appointment in a postal code starting with 1123? I have tested the Excel sheet with real data and it looks promising!

[gideon]

And one more request I have. How can I change all formulas, so that in M9 and M10 I can have dates instead of number of days from today?

[gideon]

Two more questions. How can I in column P have a list of Sundays where I can't? Sometimes it's faster to let somebody know when we can't than when we can. And: How can I in M8 have the second beginning of a postal code, so that Excel lists the Sundays where I already have an appointment in one or both of these postal codes? It already looks promising. Thanks a lot!

[HansV]

One more question I have. When I have 1123 in M7, it shows both the Sundays where I already have an appointment in a postal code starting with 1123 and the Sundays where I don't have appointments yet, at all. How can I let the O column only show those Sundays where I already have an appointment in a postal code starting with 1123? I have tested the Excel sheet with real data and it looks promising!

Sorry, no.

See the attached version.

Mappe1 (1) (4).xlsx

[p45cal]

the cells get an error message ("Numer Stored as Text"). I can change the "Error Checking Options" in my Excel, but how do I prevent that when this file is opened on another computer, it shows this error message?

If you select the cells with the error triangle, you get this:

2024-04-21_112017.jpg

then if you click on that warning triangle dropdown you get the option to ignore the error:

2024-04-21_112054.jpg

leaving you with:

2024-04-21_112121.jpg

which seems to be saved with the file.

[DocAElstein]

the cells get an error message ("Numer Stored as Text"). I can change the "Error Checking Options" in my Excel, but how do I prevent that when this file is opened on another computer, it shows this error message?

If you ..........

Hi
In/ using VBA, quite often the simple pseudo
Range(x).Value = Range(x).Value
, may be sufficient to remove the Number Stored as Text thing

( By the way, in a few threads here, example, I messed up , - I stupidly overlooked that simple solution, because I thought I was being clever doing the Evaluate Range type solution based on an Excel formula which removes the Number Stored as Text thing
As it turned out, although it worked, what was actually happening was that the Evaluate(" ") thing by virtue of the way it was being used was returning values, ( which it mostly does, but not always ).
So, as I discovered I was just returning values, and that alone was doing the job, so, then I thought, well in that case, if returning just the values is doing the job, I
Then I might as well just return values, in the most simple way ,like the pseudo
Range(x).Value = Range(x).Value thing
, and forget the "clever" Evaluate range stuff

In the demo coding below, you may be able to see what I am getting at, - in Rems 2, 3, and 4, - That goes through starting with a simple formula, then it’s going through the formula in Evaluate Range idea, showing that in fact the Evaluate(" ") itself often most likely will be just returning value, so the actual formula is a bit redundant, just the range reference messed with in any simple way to make it return values is enough, so then 5 will do instead, which is the pseudo
Range(x).Value = Range(x).Value thing

( Rem 11 I just chucked in as another way to keep a leading zero, that might work if you always have the same number of digits in a whole number. ( I notice that Rem 2 is also keeping me a leading zero, I am not quite sure why that is, and/but that might be something that could be unreliable and inconsistent ) )

Code: Select all

 Sub NumberStoredasText() '  https://eileenslounge.com/viewtopic.php?p=316585#p316585   Number Stored as Text   Number formatted as text   https://eileenslounge.com/viewtopic.php?p=272704#p272704 2    Number Stored as Text  Number formatted as text August 2020
Rem 0
Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets("NumberStoredasText")
 Ws.Range("A1:E12").Clear
Rem 1  Make a some number as Text and text examples
 Let Ws.Range("A1:E1") = Split("01 20 Text  50", " ", -1, vbBinaryCompare)   '  Split  is conveniient for this as it returns an array of string type elements

Rem 2 use a formula to get rid of the  Number Stored as Text  thingy
 Let Ws.Range("A2:E2") = "=IF(A1="""","""",IF(ISNUMBER(A1),1*A1,A1))"

Rem 3 Mistakenly try to do a clever   Eval Range  solutuon
 Let Ws.Range("A3:E3") = Ws.Evaluate("=IF(A1:E1="""","""",IF(ISNUMBER(A1:E1),1*A1:E1,A1:E1))")

Rem 4 Why was I a bit mistaken in Rem 3
' 4a  First another subtlty - this won't work because -  Evaluate("   ")  gives us a range object back in this situation (https://eileenslounge.com/viewtopic.php?f=27&t=36166&p=280951#p280951 https://eileenslounge.com/viewtopic.php?p=280951#p280951 ) . Excel is not sure, (or Microsoft weren't sure, or both)  what to do with that.    There 'aint no documentation on this, ... well apart from mine   https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly?p=23192&viewfull=1#post23192
 Let Ws.Range("A4:E4") = Ws.Evaluate("A1:E1")
' 4b  To stop  Evaluate("   ")  giving a range back  we can do a coouple of things
' 4b(i)  get the  .Value  property
 Let Ws.Range("A5:E5") = Ws.Evaluate("A1:E1").Value
' 4b(ii)  do anything to make  Evaluate("  ")  give the values back
 Let Ws.Range("A6:E6") = Ws.Evaluate("A1:E1" & "&" & """""") '  Thousands of different things can be done here.  My thinking is that  Excel and Evaluate(" ")  has been wired to return the named object initially if it can. If that don't work it adds an  "="  on to it and sees if it can make a working Excel formula. In this case it will see a valid Excel cell formula  =A1:E1&""   Now, here's the thing: in Excel most formulas built with range objects as part of them, will use the values of the range objects, since doing things like adding or concatenating are things done to values, not object, either most usually or always, - I am not sure yet aboout that last bit

Rem 5 If all I am doing is giviing the values back,  then I might as well give the values back
 Let Ws.Range("A7:E7") = Ws.Range("A1:E1").Value

Rem 11
 Let Ws.Range("A11:E11") = Ws.Range("A1:E1").Value
 Let Ws.Range("A11:E11").NumberFormat = "00"

End Sub

Results

Some Numbers Stored as Text.JPG

Alan

Ref
https://eileenslounge.com/viewtopic.php ... 23#p310023
Number stored as text, alignment of numeric values in cells