Conditional statements not holding from Mac to PC

**ibabs** · 09-23-2009, 09:41 AM

Hello,
I have some fairly simple conditional statements to format date fields based on current date and upcoming dates. They work perfect on the mac, but when my co-workers open the excel sheet on their PC's it all whacks out?
Does anyone know how to create or save these so they open properly on both platforms?
thanks you!

**ibabs** · 09-23-2009, 11:45 AM

Hello,

OK...I found the problem, but I am not sure how to rectify it...

In order to do the conditional statements, I had to go into the Tools menu , Add-Ins and turn on the following:
Analysis Tool pack and Analysis Tool pack-VBA

The PC's don't have these on by default so the conditional formatting is not working.

Is there a way to force those options to be checked when the document opens?
thanks!

barbara

**shg** · 09-23-2009, 11:49 AM

If the dates are all off by four years, change the PC date system to 1904, which is (I believe) the default date system for Mac. Tools > Options > General, ...

This will offset every date in every workbook you open ...

**ibabs** · 09-23-2009, 02:44 PM

Hello,
I am not sure that was the problem....I will check with the PC users and get back to you!!!
thanks!!
babs

**shg** · 09-23-2009, 02:55 PM

I posted before I read your follow-up.

But do check that dates agree between the two systems -- they must both use the same date system.

**ibabs** · 09-23-2009, 04:57 PM

Hello,
Ok..that is not the problem...The problem seems to be that the PC users are forced to open the document and then turn on those add-ins I mentioned in my earlier post. Then the formatting and dates work fine.
Do you know anyway to force those 2 add-ins to show up automatically?
thanks!!
babs

**martindwilson** · 09-23-2009, 05:07 PM

whats the formula your using?
it maybe possible to replace it with something that doesnt need analysis tool pack

**ibabs** · 09-23-2009, 05:14 PM

Hello,
Basically, I have set up simple conditions. I have these large schedules to follow, so I wanted whenever they opened up on someones machine, if something was due today, the format was to have a different color type and a bolder font. If it was something due within the next 2 days, it was also colored differently as to show, these are on board next...and if it was prior to todays date, it looked like something else.

They are pretty simple:
cell value is - is equal to - =TODAY()
cell value is- between =TODAY()-2 and =TODAY()-1
cell value is- less than =TODAY()-2

thanks!!!
babs

**martindwilson** · 09-23-2009, 05:17 PM

the today() function does not use analysis tool pack anyway
it must be the date system as shg points out
today 23 sept 2009 is
38617
in the 1904 date system but
40079
in the 1900 date system most commonly used
on pc's
but if you type in the date in 1904 system as say ( i'll use na style) 10/25/2009 when opened on a pc using 1900 it will change to
10/24/2005

**ibabs** · 09-23-2009, 05:23 PM

It is probably not a good idea for me to ask all the PC users to change their date system?????
If I build the functions on the PC, do you think the macs would have trouble reading the statements?
thanks!
babs

**martindwilson** · 09-23-2009, 05:47 PM

yep! as long as the two have different date systems there will be a problem
for example 10/25/09 on a mac with 1904 system the value is
38649
open that on a pc with 1900 date system
that changes to 10/24/05
but in excel 2007 it should know the workbook was created on a mac and auto change the date system to 1904.
i wonder if thats got something to do with analysis tool pack hmmm
but on further investigation if i save a workbook with 1904 system and open it again it maintains the 1904 system but other workbooks happily stay on 1900.

**shg** · 09-23-2009, 05:47 PM

Forget the functions for a moment.

If you enter a date on a spreadsheet on a PC, open it on a Mac, and it's four years different, you need to solve that problem first.

I don't have a Mac, so I don't know what happens.

**ibabs** · 09-23-2009, 05:49 PM

Hello,
I did get a PC person to offer an do some testing with me tomorrow...So hopefully we can get this resolved..Thanks for all your help and guidance, and I will keep you posted on what we find.
babs

**martindwilson** · 09-23-2009, 06:04 PM

but it is interesting that excel2007 which has the analysis tool pak included can detect it was built on a mac and adjust date system accordingly

**shg** · 09-23-2009, 06:06 PM

Does that hold equally for 2003, Martin?

**martindwilson** · 09-23-2009, 06:12 PM

dont know coz i only found a ref to 2007 stating it did it. but op seemed to think it worked ok if analysis tool pak was activate on earlier versions.
i havent a mac either but i can probably get someone who has to send me a sample.

Note 1900 is the first year in Excel for Windows, but 1904 is the first year in Microsoft® Excel for Macintosh. If you are working in Excel for Windows and you open a document created in Excel for the Macintosh, the 1904 date system is automatically selected when you open the document

from here
http://office.microsoft.com/training...RP102786111033