« Good information on the OpenOffice.org documentation wiki about regular expressions in Calc and Writer | Main | Resizing the slides in OpenOffice Impress Handouts »

February 11, 2008


I had no idea you were into Improv and Music Festivals. This is totally unrelated, but I can't figure out how to make negative dates, ones that end in B.C.

Wouldn't it in this very case be more appropriate to go with pivot tables / data pilots?

Hi KaTe,

That would be one way to go, but a little more complex. Just depends on how you like to work and what other data you'd like to see, like narrowing down to expenditures by month.


Hi spayced,

I must admit I'm stumped on negative dates, too. Nothing in the help that I could find in a quick search and google didn't really give me anything useful either.



The only way I've found (so far) to deal with B.C.E. dates mathematically is to convert them to Julian Dates, perform whatever function on them and then convert them back.

At one point I was working on some custom functions to handle the Julian conversions —to go along with some other custom date functions—but have not finished those particular ones yet.

Andrew Pitonyak's book on macros does mention B.C.E (page 28 - do a Google Book search and you can preview it) but doesn't seem to go into any detail. I believe Andrew is active on the OpenOffice macro forum. He might be kind enough to offer further insight.

If you enter -693,595 into a cell and format it as a date will give you January 1, 0001.

Unfortunately subtracting further from that date always seems to return the year 1.

If you're not familiar with Julian Dates a straightforward explanation may be found at:

Not sure if any of this helps you.


Hi Kate,

My cels range of code of article is a1:a10 but in 10 lists (list1:list10)! ; category is in cel List11.a1; cels range of numbers of article is b1:b10 but again in 10 lists (list1:list10).
How J must wrote this sumif function in this case?


Hi Kate,

"list" = sheet

Is there a way to use 'sumif' based on cell formating? eg a number being for bold?


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment

Comments are moderated, and will not appear until the author has approved them.

Your Information

(Name and email address are required. Email address will not be displayed with the comment.)

GetOpenOffice Consulting

Get Book Resources

Search This Blog