Help with a date formula

[Mustang]

Hi there,

I am having problems finding a solution to a problem I have been given. I
have a workbook which on one sheet we log:

Job No Weekly $ Start Date End Date No of days

This information is filtered through to a monthly sheet where I filter by
job number. My problem is that I know the total number of days our equipment
has been hired for which is fine if they hire something for just one billing
month or part of that month BUT we run our invoicing from 21st month to 20th
of the next month.

So if we are charging for June this would be from 21/6/09 - 20/7/09. So if
the Start date is 21/6/09 but end date is 5/5/09 - how can I ask Excel to
work out just the number of days on hire for the June billing period?
Bearing in mind that the dates could also be say 21/6/09 - 29/6/09. I hope
this makes sense!

Any ideas on how I can ask Excel to work out based on the Month - how many
days a piece of equipment has been hired for?

Any ideas would be appreciated.

 

[Max]

Think you could try using DATE

Assuming startdates in C2 down, enddates in D2 down
(where the startdates are always the 21st of the month?)

Then in E2, E2 formatted as general/number, then copied down:
=IF(D2>DATE(YEAR(C2),MONTH(C2)+1,DAY(C2)-1),DATE(YEAR(C2),MONTH(C2)+1,DAY(C2)-1)-C2+1,D2-C2+1)

Test it out and satisfy yourself that it's returning correctly
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Jacob Skaria]

With start date in C2 and end date in D2 try the below formula which will
give the number of days for the billing period of (start date month)..

=(IF(D2>DATE(YEAR(D2),MONTH(C2)+1,20),DATE(YEAR(D2),MONTH(C2)+1,20),D2))-(IF(C2<DATE(YEAR(C2),MONTH(C2),21),DATE(YEAR(C2),MONTH(C2),21),C2))+1

If this post helps click Yes

 

[Ron Rosenfeld]

Hi there,

I am having problems finding a solution to a problem I have been given. I
have a workbook which on one sheet we log:

Job No Weekly $ Start Date End Date No of days

This information is filtered through to a monthly sheet where I filter by
job number. My problem is that I know the total number of days our equipment
has been hired for which is fine if they hire something for just one billing
month or part of that month BUT we run our invoicing from 21st month to 20th
of the next month.

So if we are charging for June this would be from 21/6/09 - 20/7/09. So if
the Start date is 21/6/09 but end date is 5/5/09 - how can I ask Excel to
work out just the number of days on hire for the June billing period?
Bearing in mind that the dates could also be say 21/6/09 - 29/6/09. I hope
this makes sense!

Any ideas on how I can ask Excel to work out based on the Month - how many
days a piece of equipment has been hired for?

Any ideas would be appreciated.

In general, you can use this formula:

=SUMPRODUCT(--(MONTH(ROW(INDIRECT(Start_Date&":"&End_Date)))=MonthNumber))


So for June:

=SUMPRODUCT(--(MONTH(ROW(INDIRECT(Start_Date&":"&End_Date)))=6))



Note that for versions of Excel prior to 2007, you may run into the

"dreaded 4 Jun 2079" problem

after which this formula will no longer work )

--ron

 

[Mustang]

Thank you Max,

I have tried this and tested it against a date range of 21/5/09 - 29/08/09
and I get the answer of 31.

The missing part for me is where I can tell Excel which billing month I am
in, so if I am billing for August I am looking for the answer of 9 days.

Thanks

 

[Mustang]

Thank you Jacob

I have tried this and tested this also against a date range of 21/5/09 -
29/08/09 and I get the answer of 31 (as per Max's formula).

The missing part for me is where I can tell Excel which billing month I am
in, so if I am billing for August I am looking for the answer of 9 days.

Thanks

 

[Mustang]

Thanks Ron,

I have tried this and tested this also against a date range of 21/5/09 -
29/08/09 and I get the answer of 30.

The missing part for me is where I can tell Excel which billing month I am
in, so if I am billing for August I am looking for the answer of 9 days but
this formula gives me 29 days. Any ideas?

Thanks

 

[Ron Rosenfeld]

Thanks Ron,

I have tried this and tested this also against a date range of 21/5/09 -
29/08/09 and I get the answer of 30.

The missing part for me is where I can tell Excel which billing month I am
in, so if I am billing for August I am looking for the answer of 9 days but
this formula gives me 29 days. Any ideas?

Thanks

I cannot tell from what you write if you are getting a result of 30, or 29 from
my formula???

But from this posting, and your previous postings, your date entry format is
not clear. I had assumed it was a dd/mm/yy format, and also a dd/m/yy format,
but that, applied to your data above, would result in dates of 21 May 2009 to
29 Aug 2009 which, so far as I can see, would result in 29 days in the month of
August (which is what my formula returns). Since you are expecting only 9
days, are you trying to enter dates in a yy/mm/dd format? That would give you
a start date of 9 May 2021 and an end date of 9 Aug 2029 which would result in
9 billable days in August of 2029, but doesn't really make much sense.

So please state unambiguously what the date 21/08/09 means; and also what the
date 21/5/09 means.

So far as I can see, if the date range is 21 May 2009 to 29 Aug 2009, there
should be 29 days in August.
--ron

 

[Mustang]

Sorry I mistyped the dates. My date range is 21/5/09 - 29/08/09

I tested it for June (month 6) and got an answer of 30, which is correct for
how we bill.

Thanks

 

[Ron Rosenfeld]

Sorry I mistyped the dates. My date range is 21/5/09 - 29/08/09

I tested it for June (month 6) and got an answer of 30, which is correct for
how we bill.

Thanks

Well, then I don't understand how you got a result of 29 for the month of
August.


So as not to have to deal with typos, please post the exact formula you used.

Don't TYPE it into your response. Rather select the formula in the Excel
formula bar; then copy it and paste it into your response.

Please do the same with your start-date and end-date data.

I do get a result of 9 when using the dates you provide above,

Start Date End Date
21-May-2009 09-Aug-2009


and testing for the month of August.
--ron

 

[Mustang]

Hi Ron,

On my first sheet (Quoted Job No Master) the dates are laid out as:

H I
1
2 Start Date End Date
3 21/05/09 29/08/09

On the monthly job sheet (there is one sheet that is re-used each month) to
try and work out the number of days on hire for the billing month I have
tried your formula and for August it looks like:

=SUMPRODUCT(--(MONTH(ROW(INDIRECT('Quoted Job No Master'!H3&":"&'Quoted Job
No Master'!I3)))=8))

Hopefully you will be able to let me know what I have done incorrectly.

Thanks so much for all your help.

 

[Ron Rosenfeld]

Hi Ron,

On my first sheet (Quoted Job No Master) the dates are laid out as:

H I
1
2 Start Date End Date
3 21/05/09 29/08/09

On the monthly job sheet (there is one sheet that is re-used each month) to
try and work out the number of days on hire for the billing month I have
tried your formula and for August it looks like:

=SUMPRODUCT(--(MONTH(ROW(INDIRECT('Quoted Job No Master'!H3&":"&'Quoted Job
No Master'!I3)))=8))

Hopefully you will be able to let me know what I have done incorrectly.

Thanks so much for all your help.

OK, you are not doing anything wrong. It was ME who did not read your initial
specifications closely enough. In particular, I missed the part about your
billing month starting on the 21st day of the month and ends on the 20th day of
the succeeding month.

Let me suggest this formula:

=SUMPRODUCT((ROW(INDIRECT(StartDate&":"&EndDate))>=DATE(YEAR(K3),MONTH(K3),21))*
(ROW(INDIRECT(StartDate&":"&EndDate))<=DATE(YEAR(K3),MONTH(K3)+1,20)))

In this instance, K3 has some date (it can be any date) in the month of
interest. You could format that cell to show just the month name, or month and
year.

And by the way, this formula DOES give a result of 9 for the month of August,
which I now realize covers 21-Aug through 29-Aug inclusive.



--ron

 

[Mustang]

Thank you SO much you have been really helpful. Your time is appreciated!

 

[Ron Rosenfeld]

Thank you SO much you have been really helpful. Your time is appreciated!

You're welcome. Glad to help. Sorry for the initial mix-up.
--ron