Automatic Report to run for 6 days only

  • Thread starter Thread starter Fernie
  • Start date Start date
F

Fernie

Im wondeing if someone can help with the following.

I have an automatic report which runs daily and saves accross to
another file. These are in reference to call volumes within the work
place.

The report runs through macros within excel witch point to a 'Control'
Sheet.

The 'Control' sheet in excel has a formula in cell C2 (to say what
date it is) C2 =Now() and a Formula in cell C4 (To say what date it is
running for) C4 =TEXT(C2,"dd/mm/yyyy")-1 as it is running for the
previous day.

This works fine now but..... I was wondering if there is anyway to
stop it running for a Sunday as we do not need to report for a sunday.

I have thought about doing it the easy way by having a dummy day as
sunday in the file that it saves accross to but this is used by other
depts and i will have to change other calculations along the way.

Hope you can help
 
Hi Fernie!

If I understand correctly:

=IF(WEEKDAY(TEXT(C2,"dd/mm/yyyy")-1)=1,"",TEXT(C2,"dd/mm/yyyy")-1)

This means that you'll get a blank when the day of the date in C2 is a
Monday.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
That's correct.

Because i am wanting to run a report on the previous day and because
the businness isnt open on a Sunday, when the report runs on a monday
morning it brings back nothing.

I would like to be able to try to get the report to run for 6 days
only (Mon - Sat) so when i come in on monday morning the report shows
saturdays info.

Hope this helps

Fernie
 
Hi Fernie!

Try:

=IF(WEEKDAY(TEXT(C2,"dd/mm/yyyy")-1)=1,TEXT(C2,"DD/MM/YYYY")-2,TEXT(C2
,"dd/mm/yyyy")-1)

On Mondays it will return Saturday. But if you choose to go to work on
a Sunday it will also show Saturday.

Seasons Greetings

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,


I have tested the calculation on one of the reports that we run and so far so good.

Thanks for the help once again and a Happy New Year to you.



Fernie.
 
Hi Fernie!

Thanks for confirmation it's OK. Always useful for Google Searches to
see a suggestion works.

And a Happy New Year to you as well

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,

The formula that you sent work's great

However i have another little query...

On my 'Control Page' i have the following formulas which the macros
point to.
In cell C2 i have the current date which is =NOW()
I have the formula that you sent in cell C4 which helps the report run
each day and then duplicates saturdays infomation on a sunday as the
business is closed =IF(WEEKDAY(TEXT(C2,"dd/mm/yyyy")-1)
=1,TEXT(C2,"DD/MM/YYYY")-2,TEXT(C2,"dd/mm/yyyy")-1)
And the i have a formula in cell A9 which is the following
=C2-WEEKDAY(C2)+2.

The formula in A9 is part of my save_file macro. The problem is when
the computer runs saturdays infomation on the sunday it also wants to
save it in a new file as it thinks its the start of a new week! But
its the end of my reporting week. Is there a way i can get it to do
this on a monday morning or something cause this is still making my
report crash.

I did not set up the formula in cell A9 so i dont really know what the
+2 in it means. But i think that this is part of the problem.

Hope that this makes sense.

If not please let me know.

Many Thanks

Fernie
 
Hi Fernie!

Your mysterious formula in A9:

=C2-WEEKDAY(C2)+2

Returns the Monday of the week that C2 falls in assuming Sunday as the
first day of the week.

Logic? If you deduct the weekday number from a date it will return the
previous Saturday. If you then add 2, you'll get the Monday that
follows that Saturday. So for a Sunday date, the Weekday function
returns 1. Deduct that 1 for the date gives you the day before and
adding 2 gives the Monday.

I hope that gets you closer.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top