Excel / OpenOffice calc help.

5
Found 9th Sep 2017
Hi,

I have been playing far to much with this as I believe it should be possible, however what ever I try fails.

currently only have openoffice but a lot of excel codes work.

my problem.......

in cell G2 is have the formula =now() which brings up the current time this works fine

however I want other cells the Change depending on the time. in cell H2 I would like it to say AM if it's before 10:30 and if after 15:30 to say PM else MID

what ever I try seems to fail. so would love suggestions.

another thing I would like to do is if Cell I20 has Yes is for it to open a text box over cells E23 through K29 that's editable (while most of sheet is not) and would need to cover over formulas in those cells behind and be able to print.

is that possible and ideally possible with no script running as I'm not sure my work machine allows it?


thanks in advance for any help

  1. Ask
Groups
  1. Ask
5 Comments

Just had a mess around with the first part.

Used a nested IF statement, extracted time value from G2 and used decimal to denote 10:30 and 15:30.

=IF(TIME(HOUR(G2),MINUTE(G2),SECOND(G2))<(10.5/24),"AM",IF(TIME(HOUR(G2),MINUTE(G2),SECOND(G2))>(15.5/24),"PM","MID"))

Hope that was what you wanted.

Original Poster

mug5159 m ago

Just had a mess around with the first part.Used a nested IF statement, …Just had a mess around with the first part.Used a nested IF statement, extracted time value from G2 and used decimal to denote 10:30 and 15:30. =IF(TIME(HOUR(G2),MINUTE(G2),SECOND(G2))<(10.5/24),"AM",IF(TIME(HOUR(G2),MINUTE(G2),SECOND(G2))>(15.5/24),"PM","MID"))Hope that was what you wanted.

Will have a look at that and play with it. Thanks. Will let you know if it works how I wish

I thought open office was lagging behind with very little development. It's was forked and named libre office which has better support and compatibility. Open office has started to get some updates and support again and is many peoples first choice but it should not be because is not as good as libre office.

Original Poster

wayners4 h, 0 m ago

I thought open office was lagging behind with very little development. …I thought open office was lagging behind with very little development. It's was forked and named libre office which has better support and compatibility. Open office has started to get some updates and support again and is many peoples first choice but it should not be because is not as good as libre office.

Agree however I am limited to what my company chooses to put on the work computers. I do not have any rights to put anything else on

Original Poster

mug519th Sep

Just had a mess around with the first part.Used a nested IF statement, …Just had a mess around with the first part.Used a nested IF statement, extracted time value from G2 and used decimal to denote 10:30 and 15:30. =IF(TIME(HOUR(G2),MINUTE(G2),SECOND(G2))<(10.5/24),"AM",IF(TIME(HOUR(G2),MINUTE(G2),SECOND(G2))>(15.5/24),"PM","MID"))Hope that was what you wanted.

Works great thanks. Now need to look at next bit
Post a comment
Avatar
@
    Text

    Top Discussions

    Top Merchants