Excel form for expense reporting

[wardawg]

I want to set a formula (or macro) in a cell so that it will pick-up the last
date entered in Column B. Column B is set for dates for an expense report.
I have row/cells R/S 2&3 Merged for an underlined "Expenses From:" date field
that uses
the date from cell b8. (the first open date field for a new expense report).
I want Excel to automatically pick up the last date in a completed expense
report and report it in the Thru: field for a from/thru effect. Am using
cell U2 for this last date of line item entries. How can I get excel to pick
up the last date no matter what row the user fills out?

If you want to help even more, I then want to take the dates from R2 and U2
and in cell V1 make it into a #, i.e. if R2=01/02/07 and U2=01/30/07 then V1
will = 010207-013007.

Am I asking for the moon and the stars or just the moon here?

Thanks for all your help, I truly appreciate it. Apologies for any headache
caused!

 

[Duke Carey]

As fast as you can, select your merged cells and press Ctrl-1. Go to the
Alignment tab and unmerge your cells, then select Merge Across Selection in
the Horizontal Alignment box.

Then write on the chalk board 1000 times "I will NEVER merge cells again"

To get the last numeric (including date) entry in a column, use the formula

=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

 

[wardawg]

Need the retain the merged cells for appearances sake...mgmnt requirement.
Dont see a merge across selection...have a Center across selsection but using
that throws off the desired look. Can you offer any insight to the second
part of my post? Thanks very much!

 

[Duke Carey]

You're right, it should have been Center Across Selection. I haven't seen any
appearance attributes that merging offers over Center Across Selection
doesn't. For underlining, use Single Accounting on the Fonts tab.

Merging screws up range selection, column widths, sorting, and all sorts of
stuff. It should be used only for long text footers, and even then I prefer
text boxes.

The second part of your question is ambiguous - do you want a label that
shows the date range, or do you want the # of days in the range?

If a label use

=Text(=INDEX(B:B,MATCH(9.99999999999999E+307,B:B)),"mm/dd/yy") & " - "&
text(v1,"mm/dd/yy")

 

[wardawg]

Duke,
What we are looking for is a cell that displays bothe dates in the format:

01/02/07-01/30/07

 

[wardawg]

Duke,
What we are looking for is a cell that displays bothe dates in the format:

01/02/07-01/30/07

 

[wardawg]

Duke,
that last formula returns a "contains an error message". She is looking for
the dates to be converted to a format like this: 010107 - 020207.

Regards,
David

 

[Duke Carey]

Try:

=Text(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)),"mmddyy") & " - "&
text(v1,"mmddyy")

 

[wardawg]

Duke, appreciate your continued assistance with this. Have tried the most
recent formula but Excel is reading the V1 in the formula and entereing a
zero rather than combine the 2 dates as prev mentioned. Since V1 is an empty
field we gaet this: 050807 - 010000

with 050807 as the last date in the form. the first date we are looking to
use is 01/01/07 with a final result of: 010107 - 050807. If I use that
formula in the V1 cell Excel tells me I have a circular reference. Please
advise. Thanks!

David