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

Nick

Based on the theory that Excel struggles with negative times (early), but I
guess with deliveries you would count early as 'on time', you can achieve
most of what you want with formulae. The bit that you *will* struggle with
in Excel is live, read/write capabilities. Excel doe share workbooks, but it
would not be live and it really doesn't work well.

To the lateness, use a formula like (Expected and arrival times in B2 and C2
respectively)

=IF((C2-B2)<0,0,C2-B2)

result formatted as time. Remember if deliveries are likely to be days late
sometimes, you will need to have a date column also. (If this happens, add
the date and time columns for expected and arrival first and then subtract
one from the other), eg

=IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2))

For the standing time, just take the departure from the arrival (Say H2 and
F2)

=IF((H2-F2)<0,0,H2-F2)

formatted as time...

Hopefully this is a start


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Nick Cartwright" wrote in
message ...
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

Hi Nick many thanks for the reply,

Would it be possible to add this spread into for example an access data base
or another program ? Or have you a suggestion on how you would solve this ?

Regards
Nick

"Nick Hodge" wrote:

Nick

Based on the theory that Excel struggles with negative times (early), but I
guess with deliveries you would count early as 'on time', you can achieve
most of what you want with formulae. The bit that you *will* struggle with
in Excel is live, read/write capabilities. Excel doe share workbooks, but it
would not be live and it really doesn't work well.

To the lateness, use a formula like (Expected and arrival times in B2 and C2
respectively)

=IF((C2-B2)<0,0,C2-B2)

result formatted as time. Remember if deliveries are likely to be days late
sometimes, you will need to have a date column also. (If this happens, add
the date and time columns for expected and arrival first and then subtract
one from the other), eg

=IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2))

For the standing time, just take the departure from the arrival (Say H2 and
F2)

=IF((H2-F2)<0,0,H2-F2)

formatted as time...

Hopefully this is a start


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Nick Cartwright" wrote in
message ...
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

For a multi-user environment Access would be far more suitable, and you
could either have queries displaying data there or use Excel as a front end
reporting tool with pivot tables perhaps

You would want at least one table with all your date, arrival, etc data and
then do the calculations in a query

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Nick Cartwright" wrote in
message ...
Hi Nick many thanks for the reply,

Would it be possible to add this spread into for example an access data
base
or another program ? Or have you a suggestion on how you would solve this
?

Regards
Nick

"Nick Hodge" wrote:

Nick

Based on the theory that Excel struggles with negative times (early), but
I
guess with deliveries you would count early as 'on time', you can achieve
most of what you want with formulae. The bit that you *will* struggle
with
in Excel is live, read/write capabilities. Excel doe share workbooks, but
it
would not be live and it really doesn't work well.

To the lateness, use a formula like (Expected and arrival times in B2 and
C2
respectively)

=IF((C2-B2)<0,0,C2-B2)

result formatted as time. Remember if deliveries are likely to be days
late
sometimes, you will need to have a date column also. (If this happens,
add
the date and time columns for expected and arrival first and then
subtract
one from the other), eg

=IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2))

For the standing time, just take the departure from the arrival (Say H2
and
F2)

=IF((H2-F2)<0,0,H2-F2)

formatted as time...

Hopefully this is a start


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Nick Cartwright" wrote in
message ...
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

Good morning, Nick C.,
When I read your first post above, Access came to mind almost immediately.
It is better suited overall because of your needs to analyze the data and
for several people to be able to use the program at the same time.

The problem with Excel in doing analysis on data as you have it is that it's
difficult to collate data from numerous worksheets and I suspect there are a
lot more going to be involved than the week's worth in the sample you
provided. The other problem is the one of simultaneous use by several
people. I see a need for that here - you can't have two people scheduling
the same resource for use at the same time by two or three different people
for one thing. To prevent that, you need keep up to date on when resources
are scheduled to prevent possible conflicts. With an Access database you can
check almost instantly to see if someone has scheduled the use of something
that would potentially conflict with a booking you are in the process of
making.

Nick Hodges noted you could use Excel as a front end and as an output for
reports to be generated. True. But I'd consider building an Access
application to be used as the front end and data storage with either output
via Access reports or exported to Excel for even further data analysis.
Actually I often prefer to export to Excel because it's easier to distribute
an Excel workbook and do further analysis on it than it is an Access report.

Another nice thing about Access is that in the final product you should have
your back end, with the data tables, separated physically into a separate
..mdb file that is linked to with a front end with the business logic, user
interface, reports, etc built in to it. Then everyone uses the front end to
communicate with the centralized back end and so you have a 'real time' view
of resource allocation. You can also be making changes to the front end to
revise business rules, revise the user interface, etc. without taking the
whole thing off line.

My only reservation in recommending Access to look at would be one of size -
Access tends to get bogged down when there are huge numbers of records (as in
6-digit numbers of them) to query or of you have a very large number of
simultaneous users. It is also still limited to 2 GB for any single table or
total .mdb file size - although for most cases that's plenty and if your data
tables approach that size you've either come up with work arounds (spreading
tables across several .mdb files) or switched to a more robust solution such
as SQL Server or another database.

For your use, my initial design of the database would be centered around
making a booking one record in a table. Other tables I would probably have
would be one containing a list of all available resources (trailers, vans,
etc) and probably another one with my customer list in it. Well, just about
any of the information for a booking that could be drawn from a list of that
information would make things go smoother - insuring consistent spelling, use
of abbreviations, names, etc. which will make your data analysis more
accurate later.

"Nick Cartwright" wrote:

Hi Nick many thanks for the reply,

Would it be possible to add this spread into for example an access data base
or another program ? Or have you a suggestion on how you would solve this ?

Regards
Nick

"Nick Hodge" wrote:

Nick

Based on the theory that Excel struggles with negative times (early), but I
guess with deliveries you would count early as 'on time', you can achieve
most of what you want with formulae. The bit that you *will* struggle with
in Excel is live, read/write capabilities. Excel doe share workbooks, but it
would not be live and it really doesn't work well.

To the lateness, use a formula like (Expected and arrival times in B2 and C2
respectively)

=IF((C2-B2)<0,0,C2-B2)

result formatted as time. Remember if deliveries are likely to be days late
sometimes, you will need to have a date column also. (If this happens, add
the date and time columns for expected and arrival first and then subtract
one from the other), eg

=IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2))

For the standing time, just take the departure from the arrival (Say H2 and
F2)

=IF((H2-F2)<0,0,H2-F2)

formatted as time...

Hopefully this is a start


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Nick Cartwright" wrote in
message ...
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

Hi JLatham,

Many thanks for the great reply, I'm hopeless with access etc... Is it
possible you could develope this for me and obviously charge me accordingly ?

Best Regards
Nick

"JLatham" wrote:

Good morning, Nick C.,
When I read your first post above, Access came to mind almost immediately.
It is better suited overall because of your needs to analyze the data and
for several people to be able to use the program at the same time.

The problem with Excel in doing analysis on data as you have it is that it's
difficult to collate data from numerous worksheets and I suspect there are a
lot more going to be involved than the week's worth in the sample you
provided. The other problem is the one of simultaneous use by several
people. I see a need for that here - you can't have two people scheduling
the same resource for use at the same time by two or three different people
for one thing. To prevent that, you need keep up to date on when resources
are scheduled to prevent possible conflicts. With an Access database you can
check almost instantly to see if someone has scheduled the use of something
that would potentially conflict with a booking you are in the process of
making.

Nick Hodges noted you could use Excel as a front end and as an output for
reports to be generated. True. But I'd consider building an Access
application to be used as the front end and data storage with either output
via Access reports or exported to Excel for even further data analysis.
Actually I often prefer to export to Excel because it's easier to distribute
an Excel workbook and do further analysis on it than it is an Access report.

Another nice thing about Access is that in the final product you should have
your back end, with the data tables, separated physically into a separate
.mdb file that is linked to with a front end with the business logic, user
interface, reports, etc built in to it. Then everyone uses the front end to
communicate with the centralized back end and so you have a 'real time' view
of resource allocation. You can also be making changes to the front end to
revise business rules, revise the user interface, etc. without taking the
whole thing off line.

My only reservation in recommending Access to look at would be one of size -
Access tends to get bogged down when there are huge numbers of records (as in
6-digit numbers of them) to query or of you have a very large number of
simultaneous users. It is also still limited to 2 GB for any single table or
total .mdb file size - although for most cases that's plenty and if your data
tables approach that size you've either come up with work arounds (spreading
tables across several .mdb files) or switched to a more robust solution such
as SQL Server or another database.

For your use, my initial design of the database would be centered around
making a booking one record in a table. Other tables I would probably have
would be one containing a list of all available resources (trailers, vans,
etc) and probably another one with my customer list in it. Well, just about
any of the information for a booking that could be drawn from a list of that
information would make things go smoother - insuring consistent spelling, use
of abbreviations, names, etc. which will make your data analysis more
accurate later.

"Nick Cartwright" wrote:

Hi Nick many thanks for the reply,

Would it be possible to add this spread into for example an access data base
or another program ? Or have you a suggestion on how you would solve this ?

Regards
Nick

"Nick Hodge" wrote:

Nick

Based on the theory that Excel struggles with negative times (early), but I
guess with deliveries you would count early as 'on time', you can achieve
most of what you want with formulae. The bit that you *will* struggle with
in Excel is live, read/write capabilities. Excel doe share workbooks, but it
would not be live and it really doesn't work well.

To the lateness, use a formula like (Expected and arrival times in B2 and C2
respectively)

=IF((C2-B2)<0,0,C2-B2)

result formatted as time. Remember if deliveries are likely to be days late
sometimes, you will need to have a date column also. (If this happens, add
the date and time columns for expected and arrival first and then subtract
one from the other), eg

=IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2))

For the standing time, just take the departure from the arrival (Say H2 and
F2)

=IF((H2-F2)<0,0,H2-F2)

formatted as time...

Hopefully this is a start


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Nick Cartwright" wrote in
message ...
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

Nick, I don't know that I'd charge for providing assistance to you. Lord
knows I've given away a lot of help with Access and Excel things to many
others here and in a couple of other places I hang out. This doesn't look
like a massive project so why don't you drop me an email to 2kmaro @ dslr.net
(remove spaces to get legit email addy from that) and we can discuss details
of things - number of users, connections available for networking the
project, etc.

JLatham

"Nick Cartwright" wrote:

Hi JLatham,

Many thanks for the great reply, I'm hopeless with access etc... Is it
possible you could develope this for me and obviously charge me accordingly ?

Best Regards
Nick

"JLatham" wrote:

Good morning, Nick C.,
When I read your first post above, Access came to mind almost immediately.
It is better suited overall because of your needs to analyze the data and
for several people to be able to use the program at the same time.

The problem with Excel in doing analysis on data as you have it is that it's
difficult to collate data from numerous worksheets and I suspect there are a
lot more going to be involved than the week's worth in the sample you
provided. The other problem is the one of simultaneous use by several
people. I see a need for that here - you can't have two people scheduling
the same resource for use at the same time by two or three different people
for one thing. To prevent that, you need keep up to date on when resources
are scheduled to prevent possible conflicts. With an Access database you can
check almost instantly to see if someone has scheduled the use of something
that would potentially conflict with a booking you are in the process of
making.

Nick Hodges noted you could use Excel as a front end and as an output for
reports to be generated. True. But I'd consider building an Access
application to be used as the front end and data storage with either output
via Access reports or exported to Excel for even further data analysis.
Actually I often prefer to export to Excel because it's easier to distribute
an Excel workbook and do further analysis on it than it is an Access report.

Another nice thing about Access is that in the final product you should have
your back end, with the data tables, separated physically into a separate
.mdb file that is linked to with a front end with the business logic, user
interface, reports, etc built in to it. Then everyone uses the front end to
communicate with the centralized back end and so you have a 'real time' view
of resource allocation. You can also be making changes to the front end to
revise business rules, revise the user interface, etc. without taking the
whole thing off line.

My only reservation in recommending Access to look at would be one of size -
Access tends to get bogged down when there are huge numbers of records (as in
6-digit numbers of them) to query or of you have a very large number of
simultaneous users. It is also still limited to 2 GB for any single table or
total .mdb file size - although for most cases that's plenty and if your data
tables approach that size you've either come up with work arounds (spreading
tables across several .mdb files) or switched to a more robust solution such
as SQL Server or another database.

For your use, my initial design of the database would be centered around
making a booking one record in a table. Other tables I would probably have
would be one containing a list of all available resources (trailers, vans,
etc) and probably another one with my customer list in it. Well, just about
any of the information for a booking that could be drawn from a list of that
information would make things go smoother - insuring consistent spelling, use
of abbreviations, names, etc. which will make your data analysis more
accurate later.

"Nick Cartwright" wrote:

Hi Nick many thanks for the reply,

Would it be possible to add this spread into for example an access data base
or another program ? Or have you a suggestion on how you would solve this ?

Regards
Nick

"Nick Hodge" wrote:

Nick

Based on the theory that Excel struggles with negative times (early), but I
guess with deliveries you would count early as 'on time', you can achieve
most of what you want with formulae. The bit that you *will* struggle with
in Excel is live, read/write capabilities. Excel doe share workbooks, but it
would not be live and it really doesn't work well.

To the lateness, use a formula like (Expected and arrival times in B2 and C2
respectively)

=IF((C2-B2)<0,0,C2-B2)

result formatted as time. Remember if deliveries are likely to be days late
sometimes, you will need to have a date column also. (If this happens, add
the date and time columns for expected and arrival first and then subtract
one from the other), eg

=IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2))

For the standing time, just take the departure from the arrival (Say H2 and
F2)

=IF((H2-F2)<0,0,H2-F2)

formatted as time...

Hopefully this is a start


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Nick Cartwright" wrote in
message ...
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