An IF condition based on time

[pedro-egoli]

I currently have a cell with formula

=NOW()

This of course gives me todays date.
However, what I need is that todays date only appear after 5pm.

Background is that I take a solar reading at 5pm each day and this cell is used by a Summary sheet in a Month to Date column.

Not sure how to go about this so any help will be appreciated.

Pedro

 

[jeffreybrown]

How about something like this.

=IF(NOW()>0.70833,NOW(),"")

5:00 PM is equal to 0.70833

 

[pedro-egoli]

Thanks Jeff.
Tried that and got this answer

40641.38926

The time here in Australia is 9.20am

Pedro

 

[jeffreybrown]

Yes you will have to set a format.

Select the cell >> Crtl + 1 >> Number tab >> select a format.

In Excel a date is on the left side of the decimal and the right side is the time.

40641 is 4/8/2011

.38926 is 9:20:32 AM

 

[pedro-egoli]

Jeff,
Not quite sure how to go about this.
What I want is for the cell to be blank unless it is after 5 pm each day .

To enable me try some variations what is the number for 5pm?
Thanks
Pedro

 

[jeffreybrown]

I'm sorry about that, just wasn't thinking...try this.

=IF(MOD(NOW(),1)>TIME(17,0,0),NOW(),"")

With using the Now() function it will only refresh when you recalculate the spreadsheet or on open.

 

[pedro-egoli]

Jeff, that looks like it would work.
Thanks again for your patience.

Pedro

 

[jeffreybrown]

You're most welcome Pedro...glad it worked for you.

 

[pedro-egoli]

Attention jeffreybrown,

In April jeffrey gave me a formula that would change the date after 5pm and it was

=IF(MOD(NOW(),1)>TIME(17,0,0),NOW(),"")

Because I wanted to use the date to take away from an earlier date , the result of which transferred to another sheet in workbook as number of days passed ,I changed formula to

=IF(MOD(NOW(),1)>TIME(17,0,0),NOW(),NOW()-1

Formula is in cell C1 of 1 worKsheet and preceding date is in B1. There is a separate worksheet for each month and figures are transferred to a summary sheet.
On main worksheet (workbook summary) I use this formula to calculate number of days

=DATEDIF('APRIL  2011  '!B1,'APRIL  2011  '!C1,"d")

On Summary sheet it has become apparent that this method is not working.
"May2011" number of days is now 34 because of the formula in the "MAY 2011" worksheet C1 showing 3/6/11.

So I need a bit of assistance please.

I still need to have the date in C1 change after 5 pm and am trying to work out how to do this.
In A4:A32 of monthly sheets I enter the date via "Ctrl+;" and what i would like to have is formula of "=MAX(A4:A32)" linked with the ">TIME(17,0,0)" function.
If it is not 5 pm the date in C1 to remain the same.

Thanks

Pedro

 

[jeffreybrown]

Hi Pedro,

Not completely following what you need.

"May2011" number of days is now 34 because of the formula in the "MAY 2011" worksheet C1 showing 3/6/11.

From what I see C1 is setup to return Now() after 5:00 PM so 3/6/11 would be correct right? Not clear what is in B1.

In A4:A32 of monthly sheets I enter the date via "Ctrl+;"

May has 31 days so why a range of A4:A32 which is only 29 cells?

I understand you requirement to have the date change after 5:00 PM, but just can't reconcile what you want here or there.

Can you use one of the HTML maker's and post B1 and C1 with their formula's and what you expect after 5:00 PM? Also, please address the A4:A32 range?