Excel formula?

  Dazza40 21:43 03 Jul 2006
Locked

Hi looking for help with a spreadsheet for work. (excel 97)

Basically we enter dates and times in the cells in the following format:-
21/7 11:00-12:00.
I'm looking for a formula or similar which will show an error if when entering data in to another cell in the spreadsheet matches or overlaps any other dates and times.

e.g. if we enter 21/7 11:30-12:30 then a message or prompt will be seen on screen.

Any ideas?

Thanks

  VoG II 22:03 03 Jul 2006

Does

21/7 11:30-12:30

all go in one cell?

  Dazza40 22:10 03 Jul 2006

Hi Vog, Yes it does.

  VoG II 22:17 03 Jul 2006

Then it is going to be quite difficult.Someone else might pick this up but it's goodnight from me for the moment.

Can these 'values' be entered anywhere?

  Dazza40 22:19 03 Jul 2006

Would it be easier to enter the dates and times seperately?

  VoG II 22:22 03 Jul 2006

Yes, definately.

  Simsy 23:09 03 Jul 2006

some of the TEXT functions the entry can be split into Date and, (what I presume are), start and end times, without too much difficulty, and these can be put into different cells...

This can be done using the LEFT,MID and RIGHT functions, and appropriate formatting of cells.

However, it would be much easier, both in terms of the speadsheet formula construction, and in terms of entering the data in the first place, if you had different cells for the components to start with.

Having said that, I can't immediately think of a way of then evaluating whether an entry falls into the category you indicate, an overlap with another entry...

It's easy for a comparison with a single specified entry, but not if, (as I presume), you need the comparison to be made with all other entries already on the sheet, before coming up with a warning.

You may find help if you try posting at click here

I'll keep thinking... but I'm not too hopefull I can fully solve it!

Good luck,

Regards,

Simsy

  johnnyrocker 23:49 03 Jul 2006

what about an 'if' formula?


johnny.

  VoG II 17:11 04 Jul 2006

Do all these go in one column?

Are you looking to check that a value has not already been entered further up in the same column?

I'm not sure what you mean by overlap.

I'm thinking along the lines of a Worksheet_Change macro.

  VoG II 17:44 04 Jul 2006

Assuming the dates & times are entered as text in Column A

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range
If Target.Column <> 1 Then Exit Sub
Set Found = Columns(1).Find(Target.Value)
If Found Is Nothing Then Exit Sub
If Found.Address <> Target.Address Then MsgBox "Duplicate entry at " & Found.Address
End Sub

To use this, right click the sheet tab, View Code. Paste in the above then close the code window. The code will run whenever a new entry is made.

This will check for duplicates, not overlaps.

  silverous 18:13 04 Jul 2006

you will need to do this for a defined area.

Definitely think VoG is right with Worksheet_Change macro, then scan through the defined area where these items can be entered. Otherwise you would have to scan from cell A1 to zzzzz66536 or whatever to see what has already been entered.

One thing I'm not sure about is year. You didn't have a year in your example, I presume that a new sheet will be started for an additional year...e.g. 21/7 then a year later 21/7 is entered...excel will see both as same.

I'll have a play around with something but give some thought to i) defining the area these entries can be made (a named range / specific column?) and ii) What about years.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

What is ransomware and how do I protect my PC from Petya?

Microsoft Surface Studio – the artist's hands-on review

Original iPhone review

Comment mettre à jour Kodi ?