Easily format cells to bold and a certain background color

classic Classic list List threaded Threaded
5 messages Options
pda123 pda123
Reply | Threaded
Open this post in threaded view
|

Easily format cells to bold and a certain background color



I
          record the weather for each day of the year....it's a hobby.


It
          would be simplest if Calc could format each Sunday in bold
          font and a gray background.  With many Sundays in a year it
          takes too long to manually change each year's Sundays.


I have
          sample Calc sheet I use but don't know where I can upload it
          so you can take a look at it.




Thanks,




Peter
          Dutton


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
remygauthier remygauthier
Reply | Threaded
Open this post in threaded view
|

Re: Easily format cells to bold and a certain background color

Hi,
I am assuming your dates are in column "A", and the data in columns B
and next. You can easily do this with conditional formatting:

1) Select your dates and data
2) From the Format menu > Conditional
3) On the Conditional Formatting panel, in the first drop-down, select
"Formula is"
4) Enter the formula: WEEKDAY(INDIRECT(ADDRESS(ROW(),1)),2)=7
5) Select a style to apply. I would say "Accent 3" is the one you are
looking for, but you can create your own and select it there
6) Hit OK. Your Sundays should now be shown in Accent 3 (bold with grey
background)

What the formula does:

WEEKDAY(date,2): returns the day of week, with Sunday being day 7
ADDRESS(ROW(),1): Dynamically creates the cell address of your date; if
you are on the second row, this formula returns A2
INDIRECT(): Returns the content of; so INDIRECT(ADDRESS(ROW(),1)) on
row 2 returns the content of A2 (a date since your dates are in column
A)
WEEKDAY()=7: Sunday

If you want to upload a file, you can go directly on the
nabble.documentfoundation.org page. I believe you can upload a file
with your message from there. You can also send it to me directly if
you're stuck and I'll give you a hand.
I hope this helps.

Rémy.

Le mercredi 18 novembre 2020 à 18:40 -0500, . a écrit :

>
> I
>           record the weather for each day of the year....it's a hobby.
>
>
> It
>           would be simplest if Calc could format each Sunday in bold
>           font and a gray background.  With many Sundays in a year it
>           takes too long to manually change each year's Sundays.
>
>
> I have
>           sample Calc sheet I use but don't know where I can upload it
>           so you can take a look at it.
>
>
>
>
> Thanks,
>
>
>
>
> Peter
>           Dutton
>
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Easily format cells to bold and a certain background color

In reply to this post by pda123
If I understand what you want, and that cells have the date value in them.
See if this is what you are trying to do?
In Cell A1 put =now()
in Cell A2 put =a1+1
Copy A2 to A3:A33
Highlight Range A1:A33
Format/Conditional/Condition
Select Formula is
Enter Formula WEEKDAY($A1)=1
I added a new style GrayBold that set font to bold, and background to
gray4
Than OK.


On 18 Nov 2020 at 18:40, . wrote:

From:           "." <[hidden email]>
Subject:         [libreoffice-users] Easily format cells to bold and a
certain background color
To:             [hidden email]
Date sent:       Wed, 18 Nov 2020 18:40:04 -0500

>
>
> I
>           record the weather for each day of the year....it's a hobby.
>
>
> It
>           would be simplest if Calc could format each Sunday in bold
>           font and a gray background.  With many Sundays in a year it
>           takes too long to manually change each year's Sundays.
>
>
> I have
>           sample Calc sheet I use but don't know where I can upload it
>           so you can take a look at it.
>
>
>
>
> Thanks,
>
>
>
>
> Peter
>           Dutton
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor (Retired)    
 mailto:[hidden email]                            
 mailto:[hidden email]
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Easily format cells to bold and a certain background color

In reply to this post by remygauthier
=On 18 Nov 2020 at 19:23, Remy Gauthier wrote:


Subject:;Re: [libreoffice-users] Easily format cells to bold and a
certain
&#
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

remygauthier remygauthier
Reply | Threaded
Open this post in threaded view
|

Re: Easily format cells to bold and a certain background color

Hi,
Call it occupational hazard. I work with airlines where day 1 is
Monday. Whenever I create DOW formulas, I will always use the option
that will make Monday - Sunday as days 1-7. The default option is
definitely fine and my usage was a bit of an overkill in this specific
example.
Regards,Rémy.
Le jeudi 19 novembre 2020 à 12:59 +1000, Michael D. Setzer II a écrit :

> On 18 Nov 2020 at 19:23, Remy Gauthier wrote:
>
>
>
> Subject:               Re: [libreoffice-users] Easily format cells to
> bold and a
> certain
>                              background color
> From:                   Remy Gauthier <[hidden email]>
> To:                       [hidden email],
> [hidden email]
> Date sent:            Wed, 18 Nov 2020 19:23:52 -0500
>
>
>
> > Hi,
> > I am assuming your dates are in column "A", and the data in columns
> B
> > and next. You can easily do this with conditional formatting:
> >
> > 1) Select your dates and data
> > 2) From the Format menu > Conditional
> > 3) On the Conditional Formatting panel, in the first drop-down,
> select
> > "Formula is"
> > 4) Enter the formula: WEEKDAY(INDIRECT(ADDRESS(ROW(),1)),2)=7
> > 5) Select a style to apply. I would say "Accent 3" is the one you
> are
> > looking for, but you can create your own and select it there
> > 6) Hit OK. Your Sundays should now be shown in Accent 3 (bold with
> grey
> > background)
> >
> > What the formula does:
> >
> > WEEKDAY(date,2): returns the day of week, with Sunday being day 7
> > ADDRESS(ROW(),1): Dynamically creates the cell address of your
> date; if
> > you are on the second row, this formula returns A2
> > INDIRECT(): Returns the content of; so INDIRECT(ADDRESS(ROW(),1))
> on
> > row 2 returns the content of A2 (a date since your dates are in
> column
> > A)
> > WEEKDAY()=7: Sunday
>
>
>
> Interesting that you used weekday with the non-default option??
>
>
>
>
>
>
>
> WEEKDAY
>
>
>
> Returns the day of the week for the given date value. The day is
> returned as an
> integer between 1 (Sunday) and 7 (Saturday) if no type or type=1 is
> specified. For
> other types, see the table below.
>
>
>
>
>
>
> Syntax
>
>
>
>
>
> WEEKDAY(Number; Type)
>
> Number, as a date value, is a decimal for which the weekday is to be
> returned.
>
> Type is optional and determines the type of calculation.
>
>    
>        
>        
>    
>    
>    
>    
>     Type
>    
>    
>    
>     Weekday number returned
>    
>    
>    
>    
>     1 or omitted
>    
>    
>    
>     1 (Sunday) through 7 (Saturday). For compatibility with Microsoft
> Excel.
>    
>    
>    
>    
>     2
>    
>    
>    
>     1 (Monday) through 7 (Sunday).
>    
>    
>    
>    
>     3
>    
>    
>    
>     0 (Monday) through 6 (Sunday)
>    
>    
>    
>    
>     11
>    
>    
>    
>     1 (Monday) through 7 (Sunday).
>    
>    
>    
>    
>     12
>    
>    
>    
>     1 (Tuesday) through 7 (Monday).
>    
>    
>    
>    
>     13
>    
>    
>    
>     1 (Wednesday) through 7 (Tuesday).
>    
>    
>    
>    
>     14
>    
>    
>    
>     1 (Thursday) through 7 (Wednesday).
>    
>    
>    
>    
>     15
>    
>    
>    
>     1 (Friday) through 7 (Thursday).
>    
>    
>    
>    
>     16
>    
>    
>    
>     1 (Saturday) through 7 (Friday).
>    
>    
>    
>    
>     17
>    
>    
>    
>     1 (Sunday) through 7 (Saturday).
>    
>
>
>
>
> Thought I had done something wrong.
> I used the formula Weekday($a1)=1 in my example and it works using
> the default return values. I changed range to A1:B33 and had the
> actual
> =weekday(a1) formula in b1-b33 to validate. It then changes the
> format
> for both column a and b for the Sundays.
>
>
>
> >
> > If you want to upload a file, you can go directly on the
> > nabble.documentfoundation.org page. I believe you can upload a file
> > with your message from there. You can also send it to me directly
> if
> > you're stuck and I'll give you a hand.
> > I hope this helps.
> >
> > Rémy.
> >
> > Le mercredi 18 novembre 2020 à 18:40 -0500, . a écrit :
> > >
> > > I
> > >           record the weather for each day of the year....it's a
> hobby.
> > >
> > >
> > > It
> > >           would be simplest if Calc could format each Sunday in
> bold
> > >           font and a gray background.  With many Sundays in a
> year it
> > >           takes too long to manually change each year's Sundays.
> > >
> > >
> > > I have
> > >           sample Calc sheet I use but don't know where I can
> upload it
> > >           so you can take a look at it.
> > >
> > >
> > >
> > >
> > > Thanks,
> > >
> > >
> > >
> > >
> > > Peter
> > >           Dutton
> > >
> > >
> >
> > --
> > To unsubscribe e-mail to: [hidden email]
> > Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more:
> https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
>
>
>
>  
>
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy