Plug most recent Sunday date into a cell 2

[BobJacksonNCI]

I CAN figure this out ... I think ... but maybe someone has a clean and easy way.

Our pay periods (multiple locations) end on Sundays.
Accounting staff presently post G/L entries related to payrolls by manually typing from a payroll report.
I've developed a spreadsheet with a bit of VBA that takes the exported form of the payroll report and creates a file that can be imported - to alleviate the typing.

In cell A2, the most recent Sunday date needs to be loaded before creating the GL import file, and it must be specifically formatted. E.G., APR-23-2006

So, two things to accomplish:

1. Calculate the date. I could start with NOW(), check the day of week, if not Sunday (1), reduce by 1 and check again.

2. Possibly more difficult, force the result to be formatted exactly like APR-23-2006, upper case month, dashes and four digit year.

As always, your assistance IS appreciated!
Bob

 

[GlennUK]

A formula will do this:

=TEXT(TODAY()-WEEKDAY(TODAY())+1,"MMM-DD-YYYY")

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[tsh73]

something like
=now()-dayIfWeek(now,1)+1

(you look correct spelling - my Excel speaks Russian)

and format like
MMM-DD-YYYY

Sorry, cannot help with making month capital.

 

[tsh73]

Glenn beat me by a minute
Ok, so add upper() to his solution, and here you are

 

[BobJacksonNCI]

Borrowing from Staples ...

"That was easy"

Thanks and stars to both!

Finally usage is:

=UPPER(TEXT(TODAY()-WEEKDAY(TODAY())+1,"MMM-DD-YYYY"))

 

[AnotherHiggins]

For what its worth, I usually use a variation of this formula:
[tab][COLOR=blue white]=INT((now()-1)/7)*7+1[/color]
That will display the most recent Sunday. To control formatting, just go to Format > Cells > Number > Custom and type in MMM-DD-YYYY.

I would personally avoid using Glenn's formula (no offense, mate) because it returns a text string, not a date. Of course you could get around that by wrapping the whole thing in Value(), but then it is even longer.

To understand how and why 'my' formula works, you must understand that in Excel dates are stored as whole numbers (the number of days since Jan 1 1900) and times are stores as fractions of a day. See faq68-5827 for more info.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[BobJacksonNCI]

Uh, one problem, John ...

The month HAS to be uppercase.

Custom format doesn't do it and UPPER requires text.

Couldn't make tsh73's suggestion work ...
Maybe he has an add-in that does dayIfWeek

Thanks everyone for assisting!
Bob

 

[tsh73]

I'm sorry "dayIfWeek" was a typoo. As I've said my Excel speaks Russian so formulas looks different (though do exactly the same thing). My solution was actually
=TODAY()-WEEKDAY(TODAY(),1)+1
- but I had to guess English equivalents.
(for your information, while substituting Cyrillics to corresponding Latins, it looked for me
=SEGODNIA()-DENNED(SEGODNIA();1)+1
)
Now I read the FAQ here and know how to get it translated right without guessing )