cancel
Showing results for 
Search instead for 
Did you mean: 

Spreadsheet advice

N/A

Spreadsheet advice

Hi guys,

I'm not sure where to post this but I'm looking for some assistance/guidance
on what to do with a spreadsheet we are current'y using.

We use a 'booking' sheet at work which has a series of colums which has
fields such as booking ref,booking time,arrival time,departure time etc... of
the vehicles that have been in and out of our warehouse.

What we require is for this spreadsheet to become a little more 'advanced'
and be a little easier to use and also generate reports for example :

Vehicle 1 for customer A had a booking time of 09:00, it didn't arrive till
09:45 and left at 10:15.

We'd like for that 'Customer' how many booking slots were missed and by how
long and they how long it took for the vehicle to leave in this case it would
have been :

45 minutes late
30 minutes 'turn around time'

Also one of the main things we require is multiple users can see/edit live
data which is not possible at the moment.

You can find the spreadsheet here on my webspace
http://www.nickyboyc.force9.co.uk/BOOKING.xls

Any thoughts and suggestions will be very much appreciated

Many thanks in advance
Nick
2 REPLIES
N/A

Spreadsheet advice

Two very simple formulas will provide you with the extra info you require.
These are
=IF(AND(D5<>"",E5<>"",E5>D5),E5-D5,"")
=IF(AND(E5<>"",F5<>""),F5-E5,"")
Also ensure the formatting is set to 'Time' otherwise you'll just get funny number.

I'm sure these may look odd to you so I'll explain each part so that you can create more like them.


1) All the numbers relate to the line number they are on. In this case put these formulas into an empty field on line 5, then just copy/paste to the following lines.

2) I have combined 2 formulas together to make it work better but I'll expand them for info.
On the first formula it is made up of;
AND(D5<>"",E5<>"",E5>D5)
and
=IF(something,E5-D5,"")

3) Taking the AND statement first, the syntax is;
AND(statement1, statement2, statement3 etc)
basically all the statements must be true for the whole AND statement to be true.
In this case we don't want cell D5 (booked) to be blank, or cell E5 (arrived) to be blank, and the arriveal time must be after the booked time (E5>D5).
(> means greater than, <> means not equal to).
I have put this in so that you will only get a result if all the valid information is present, otherwise if you leave the booked field empty, and still put an arrival time in, then it will show as late.

4) Taking the IF statement, the syntax is;
=IF(query, true result, false result)
In our case if the AND statement, in 3), is true (ie all fields filled and it's late arrival), then it will show the time difference (E5-D5), otherwise it will show nothing ("". where 2 sets of " mean an empty field)

5) The second formula is very similar
=IF(AND(E5<>"",F5<>""),F5-E5,"")
consisting of and AND (AND(E5<>"",F5<>"") ensuring neither field is blank
and an IF statement, =IF(and query, F5-E5,"")
showing the difference between F5 (Departure time) and E5 (Arrival time)


If this doesn't make sense (and lots of technical gobbledygook like this often doesn't to me either :roll: ) just copy the 2 formulas into an emty field in row 5 and see what you get
(Remember to format the cells to 'TIME').

All the best.
Pendragon
Rising Star
Posts: 425
Thanks: 3
Fixes: 1
Registered: 07-04-2007

Spreadsheet advice

Brilliant set of instructions, even I can understand it and I don’t use spreadsheets in anger.

Quote
Also one of the main things we require is multiple users can see/edit live
data which is not possible at the moment.


You don’t say which version of Excel you are using but on my version (2002 SP3), if you load the spreadsheet and then go to ‘Tools’, ‘Share Workbook’ you can just tick a box to allow multiple users.

It is best if you make a copy first and put this into a shared folder then point everyone who needs access to it to this location. Every now and then back up the spreadsheet to another secure folder (or CD RW) just in case something goes wrong.

Hope that helps?