| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a hotel billing system...
I have to create a billing system as part of some ict coursework, it needs to
be able to know automatically how many weekend nights there are between set dates. ive tried loads of formulas but none work, can anyone help? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a hotel billing system...
Use something like
=A2-A1-NETWORKDAYS(A1,A2) where A1 is the start date and A2 is the end date. The NETWORKDAYS is part of the Analysis Tool Pak add-in, so you must have this loaded to use the function. Go to the Tools menu, choose Add-Ins, and select Analysis Tool Pak from the list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "matt" wrote in message ... I have to create a billing system as part of some ict coursework, it needs to be able to know automatically how many weekend nights there are between set dates. ive tried loads of formulas but none work, can anyone help? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a hotel billing system...
IT COMES UP WITH A DATE IN 1975! (WHAT DO THE a1 and a2 at the end mean and
dont i have ti define the network days) "Chip Pearson" wrote: Use something like =A2-A1-NETWORKDAYS(A1,A2) where A1 is the start date and A2 is the end date. The NETWORKDAYS is part of the Analysis Tool Pak add-in, so you must have this loaded to use the function. Go to the Tools menu, choose Add-Ins, and select Analysis Tool Pak from the list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "matt" wrote in message ... I have to create a billing system as part of some ict coursework, it needs to be able to know automatically how many weekend nights there are between set dates. ive tried loads of formulas but none work, can anyone help? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a hotel billing system...
SOrry to pester. how do i define which days are holidays
"Chip Pearson" wrote: Use something like =A2-A1-NETWORKDAYS(A1,A2) where A1 is the start date and A2 is the end date. The NETWORKDAYS is part of the Analysis Tool Pak add-in, so you must have this loaded to use the function. Go to the Tools menu, choose Add-Ins, and select Analysis Tool Pak from the list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "matt" wrote in message ... I have to create a billing system as part of some ict coursework, it needs to be able to know automatically how many weekend nights there are between set dates. ive tried loads of formulas but none work, can anyone help? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a hotel billing system...
Somewhere in your workbook create a column of known holiday dates. Select the
column of dates and click on NAME, DEFINE and name the selection HOLIDAYS (or anything you want really). In your formula example the start date is A1 and the end date is a2. Add the named range to the formula as the third (and optional) argument as in the example below: The NETWORKINGDAYS(A1,A2,HOLIDAYS) -- Kevin Backmann "matt" wrote: SOrry to pester. how do i define which days are holidays "Chip Pearson" wrote: Use something like =A2-A1-NETWORKDAYS(A1,A2) where A1 is the start date and A2 is the end date. The NETWORKDAYS is part of the Analysis Tool Pak add-in, so you must have this loaded to use the function. Go to the Tools menu, choose Add-Ins, and select Analysis Tool Pak from the list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "matt" wrote in message ... I have to create a billing system as part of some ict coursework, it needs to be able to know automatically how many weekend nights there are between set dates. ive tried loads of formulas but none work, can anyone help? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a hotel billing system...
aghh yes it works!!
ty all "Kevin B" wrote: Somewhere in your workbook create a column of known holiday dates. Select the column of dates and click on NAME, DEFINE and name the selection HOLIDAYS (or anything you want really). In your formula example the start date is A1 and the end date is a2. Add the named range to the formula as the third (and optional) argument as in the example below: The NETWORKINGDAYS(A1,A2,HOLIDAYS) -- Kevin Backmann "matt" wrote: SOrry to pester. how do i define which days are holidays "Chip Pearson" wrote: Use something like =A2-A1-NETWORKDAYS(A1,A2) where A1 is the start date and A2 is the end date. The NETWORKDAYS is part of the Analysis Tool Pak add-in, so you must have this loaded to use the function. Go to the Tools menu, choose Add-Ins, and select Analysis Tool Pak from the list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "matt" wrote in message ... I have to create a billing system as part of some ict coursework, it needs to be able to know automatically how many weekend nights there are between set dates. ive tried loads of formulas but none work, can anyone help? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a hotel billing system...
OH NOW ITS STOPPED WORKIN AGAIN, IT WORKS SOMEOF THE TIME, BUT WHEN I do like
10/02/2006 to 12/02/2006 it still only says 1shudnt it be 2 nights? "Kevin B" wrote: Somewhere in your workbook create a column of known holiday dates. Select the column of dates and click on NAME, DEFINE and name the selection HOLIDAYS (or anything you want really). In your formula example the start date is A1 and the end date is a2. Add the named range to the formula as the third (and optional) argument as in the example below: The NETWORKINGDAYS(A1,A2,HOLIDAYS) -- Kevin Backmann "matt" wrote: SOrry to pester. how do i define which days are holidays "Chip Pearson" wrote: Use something like =A2-A1-NETWORKDAYS(A1,A2) where A1 is the start date and A2 is the end date. The NETWORKDAYS is part of the Analysis Tool Pak add-in, so you must have this loaded to use the function. Go to the Tools menu, choose Add-Ins, and select Analysis Tool Pak from the list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "matt" wrote in message ... I have to create a billing system as part of some ict coursework, it needs to be able to know automatically how many weekend nights there are between set dates. ive tried loads of formulas but none work, can anyone help? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a hotel billing system...
its realli touchy, weneva i put hloidays in it just stays as one, and also
doesnt it count the day of the start? "matt" wrote: I have to create a billing system as part of some ict coursework, it needs to be able to know automatically how many weekend nights there are between set dates. ive tried loads of formulas but none work, can anyone help? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a hotel billing system...
Because networkdays counts both start and end dates you need to adjust the formula slightly to get correct results in all cases =A2-A1-NETWORKDAYS(A1,A2)+1 an alternative without NETWORKDAYS.... =SUM(INT((WEEKDAY(A1-{0,1})+A2-A1)/7)) although you can't accommodate holidays (easily) with this -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=511205 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to format a cell to represent Indian Currency System? | Excel Discussion (Misc queries) | |||
| Not enough system resources to display completely. | Excel Discussion (Misc queries) | |||
| Microsoft should put operating system on hardware not software | Excel Discussion (Misc queries) | |||
| I need a system for keeping up with church members and finances? | Excel Worksheet Functions | |||
| excel causing system to be in low system resource | Excel Discussion (Misc queries) | |||
Linear Mode
