ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Excel 97 date diff (https://www.excelbanter.com/excel-worksheet-functions/65883-excel-97-date-diff.html)

[email protected]

Excel 97 date diff
 
Hi there
I'm using Excel 97. No choice in the matter, the business is supposed
to be upgrading in a few months.

The spreadsheet is being used as a booking/scheduling system. Rooms
being booked for days at a time.

I've been reading through the archives on Google and some of the MVP
links to see if I can solve a problem. Lots of interesting stuff, but
I haven't found the answer yet.
Basically I want to print the month name in the row above another row
of dates. I only want to do this when the month changes.

This formula does work up to a point:
=IF(MONTH(E$2)-MONTH(D$2)0,TEXT(E$2,"MMM"),"")

The problem is the text is truncated to column width. The cell is set
up as general format. All cells are blank except those at the start of
the month. I had expected the left cell's contents to be seen up to
the point where was data in a cell to the right.

I tried
=IF(MONTH(E$2)-MONTH(D$2)0,TEXT(E$2,"MMM"))
which evalutates to FALSE, but due to narrow cell width, they are
displayed as #

Any comments or thoughts?
Thanks
-ao-


[email protected]

Excel 97 date diff
 
I should add, I did try the DateDif(..."m") function and variations
This didn't do what I want which is to mark the transition from one
month to the next.


[email protected]

Excel 97 date diff
 

wrote:
I should add, I did try the DateDif(..."m") function and variations
This didn't do what I want which is to mark the transition from one
month to the next.


Why not put the date MMM on the row you want in all cells but set
conditional formula to show the font colour white if cell A5 =B5 that
way you will only see it if the month changes

Hope you get the drift


[email protected]

Excel 97 date diff
 
Tried that, but the text gets truncated as the adjacent cell is not
empty, so cell contents displayed as #
I've changed forumula to
IF(MONTH(E$2)<MONTH(D$2),TEXT(E$2,"MMM"))

I have a start date in Cell A1 and then in the scheduling section, cell
B2=A1, C2=B2+1, etc.
Cells B3:... are =Text(B2,"ddd") with conditional format, pattern=grey
if value ="Sat" or "Sun"

I've not done much with PivotTables, but they may offer a solutiuon, so
I'm going to look into that.
-ao-



All times are GMT +1. The time now is 04:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com